EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL TO_DATE()

PostgreSQL TO_DATE()

Priya Pedamkar
Article byPriya Pedamkar

Updated May 29, 2023

PostgreSQL TO_DATE()

Introduction to PostgreSQL TO_DATE()

PostgreSQL TO_DATE() function is used to convert a literal string into a date value; the to_date function will convert the string into a date. We have given two input parameter arguments with the to_date function in PostgreSQL, i.e., text and format; the to_date function is very useful and important in PostgreSQL to convert the string into a date value. To_date function returns the date from the function as we have provided input parameters from the to_date function in PostgreSQL. We use the to_date function to handle input formats that simple casting cannot convert. This function interprets input with minimal error checking.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Below is the syntax of the to_date function in PostgreSQL.

  • to_date (text, format)OR
  • to_date (text, text)OR
  • to_date (string, format)

Below is the parameter description syntax of the to_date function in PostgreSQL.

  • to_date – We use the to_date function to convert a date represented as a character string to a date data type.
  • Text –Text is the first parameter accepted by the to_date function in PostgreSQL. Text is a string argument, which we have converted into the date.
  • Format –The second argument is the input format of the to_date function, which returns the date value. It will define the input string format in the form of date parts.
  • String – We define the string argument as the string we converted into the date. We have also defined a string as text in the to_date function.

Working of PostgreSQL TO_DATE() function

  • Below is the working of the to_date function in PostgreSQL.
  • The main use of the to_date function in PostgreSQL is to convert the string lateral into the date; the to_date function is used to convert the string into date format.
  • We have pass two arguments with the to_date function in PostgreSQL, i.e., string and format.
  • We can format the date value using the to_date function in PostgreSQL as follows.

Patterns for formatting date values using the to_date function in PostgreSQL.

  1. Y,YYY – We define this as a four-digit year separated by a comma after the first two digits.
  2. YYYY – This format will define as the year in four digits. We have not used any string to divide the year.
  3. YYY – This format will define the last three digits of the year.
  4. YY – This format will define the last two digits of the year.
  5. Y – This format will define the last one digits of the year.
  6. IYYY – This is defined as the ISO numbering of the year. It will display the last four or more digits as output.
  7. IYY –This format of ISO numbering will define the last three digits of the week numbering year.
  8. IY – This format of ISO numbering will define the last two digits of the week numbering year.
  9. I – It will define the last digit of the ISO 8601 week numbering year.
  10. MONTH – Used to specify the uppercase version of the English month name.
  11. Month – We define this as a fully capitalized English month name.
  12. month – Define this as a fully lowercase English month name.
  13. MON –This is an abbreviated uppercase English month name, i.e., JAN, FEB.
  14. Mon – An abbreviated capitalized English month name, i.e., Jan, Feb.
  15. mon – This is an abbreviated lowercase English month name, i.e., Jan, feb.
  16. MM – We define this as the month number, ranging from 0 to 12.
  17. DAY – Define this as a day name in full uppercase.
  18. Day – We define this as a day name in fully capitalized form.
  19. day – We define this as the day name in fully lowercase form.
  20. DY – It is an abbreviated uppercase day name.
  21. Dy – We define this as a capitalized abbreviated day name.
  22. dy – An abbreviated lowercase day name.
  23. DDD – We define this as the day of the year, ranging from 000 to 366.
  24. DD – We define this as the day of the month, ranging from 01 to 31.
  25. D – We define the day of the week with Sunday represented as 1 and Saturday represented as 7.
  • We can convert the date using the to_date function as we specified a format; we can use any format as above in the to_date function.
  • If suppose we have to convert the string as 2020 May 20 into a date value, we can use the pattern as “DD Mon YYY” or “YYYY Mon dd”.

Examples

Below is an example of the to_date function in PostgreSQL.

Example #1 – Convert the string using the ‘YYYY MM DD’ format

We have converted the ‘2020 05 20’ date using the ‘YYYY MM DD’ format in the below example.

SELECT to_date('2020 05 20', 'YYYY MM DD');

Output:

PostgreSQL TO_DATE() output 1

Example #2 – Convert string using the ‘DD MON YYYY’ format

We have converted the ’15 MAY 2020′ date using the ‘DD MON YYYY’ format in the below example.

SELECT to_date('15 MAY 2020', 'DD MON YYYY');

Output:

PostgreSQL TO_DATE() output 2

Example #3 – Convert the string using the ‘YYYY Mon DD’ format

In the below example, we have converted the ‘2020 May 20′ date using’YYYY Mon DD’ format.

SELECT to_date('2020 May 20','YYYY Mon DD');

Output:

PostgreSQL TO_DATE() output 3

Example #4 – PostgreSQL to_date gotchas

In the below example, we have passed an invalid date string to the to_date function. After passing an invalid date sting, we converted the string into the date format.

SELECT to_date('2020/05/20', 'YYYY/MM/DD');

Output:

output 4

Example #5 – Convert the string using’MMDDYY’ format

We have converted the ‘052020’ date using the ‘MMDDYY’ format in the below example.

SELECT to_date('052020', 'MMDDYY');

Output:

output 5

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL TO_DATE()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL While Loop
  2. PostgreSQL replace
  3. PostgreSQL For Loop
  4. log PostgreSQL