PostgreSQL TO_DATE() Function: Convert String to Date

PostgreSQL provides a built-in function named TO_DATE() that assists us in converting a string into a date. It accepts a string and a format as an argument and converts the given string according to the specified format. The TO_DATE() function retrieves a date value in “YYYY-MM-DD” format.

This article will illustrate the usage of the TO_DATE() function with practical examples. So, let’s get started.

How to Use TO_DATE() Function in Postgres?
The below snippet shows the syntax of the TO_DATE() function:

TO_DATE(str, format);

The above snippet demonstrates that the TO_DATE() function accepts two arguments: str and a format. Based on the given format/pattern, the provided string will be converted to a date value. The TO_DATE() function accepts only a valid format based on which the given string will be converted into a date value.

The list of valid date formats/patterns is provided below:

• YYYY: Indicates the Year in four digits/figures.
• YYY: To specify the Year in three digits.
• YY: Specifies the Year in two digits.
• Y: To specify only the last digit of the year.
• Y,YYY: Specifies the Year in four digits; the first digit will be separated with a comma.
• IYYY: ISO standard 4-digit year.
• IYY: ISO standard 3-digit year.
• IY: Specifies the year in two digits ISO standard.
• I: Indicates only the last digit of the year as per ISO standard.
• Q: Used to specify a quarter (1 quarter = 3 months, e.g., Jul-Sep).
• MM: Specifies a month in two digits(01-12; e.g., JAN = 01, DEC =12).
• MONTH: Month in Uppercase Letters.
• Month: Capitalized(first letter capital) month name.
• month: Month name in lowercase.
• MON: First three letters of a month in uppercase (e.g., DEC).
• Mon: First three letters of the month(capitalized), e.g., Nov, Dec, etc.
• mon: First three letters of the month in lowercase, e.g., nov, dec, etc.
• RM: To specify the month in uppercase roman numerals e.g., IX, X, XI, etc.
• rm: Specifies the month in lowercase roman numerals e.g., ix, x, xi, etc.
• W: Week number of month (1-5).
• WW: Week number of year (1-53).
• IW: Week number according to ISO 8601 standards.
• DAY: Specifies a day in uppercase letters.
• Day : Specifies a capitalized(first letter capital) day.
• day: Specifies the day name of day in lowercase letters.
• DY: Abbreviated day name in uppercase letters.
• Dy: Abbreviated capitalized(First letter capital) day name.
• dy: Abbreviated day name in lowercase letters.
• DDD: Day of the year (001-366).
• IDDD: Day of a year according to ISO.
• DD: Day of a month(01-31).
• D: Day of week (1-7, here 1 represents Sunday, 2 for Monday, … and 7 represents Saturday)
• ID: Day of the week according to ISO year (1-7, here 1 represents Monday, 2 represents Tuesday, and so on.)
• J: Julian day; i.e., no. of days since Nov 24, 4714 BC.
• AD, A.D, a.d, ad: AD indicator.
• BC, B.C, b.c, bc: BC indicator.
• CC: Specifies a century in two digits.

Example #1: How Does the TO_DATE() Function Work in Postgres?
Let’s pass a string and a format to the TO_DATE() function and see how it works:

SELECT TO_DATE('2022-01-01','YYYY-MM-DD');

We passed a string “2022-01-01” as the first argument and a format “YYYY-MM-DD” as a second argument. Consequently, we will get the following resultant output:

The output shows that the given string has been converted into the date value.

Example #2: Converting a String Into a Date Value Using TO_DATE() Function
Here is another example that illustrates the working of TO_DATE() function:

SELECT TO_DATE('01 January 2022','DD Month YYYY');

The provided string has been successfully converted into a date value.

Example #3: How to Use the TO_DATE() Function on Table’s Data?
We have already created a table named employee_details, whose details are depicted in the following snippet:

SELECT * FROM employee_details;

The emp_joining_date column has string type data. Let’s utilize the TO_DATE() function to convert the type of emp_joining_date column from text to date:

SELECT emp_name, TO_DATE(emp_joining_date, 'YYYY-MM-DD') 
FROM employee_details;

The data type of the selected column has been converted from string to date.

Conclusion
The TO_DATE() is built-in function in Postgres that assists us in converting a string value to a date value. It accepts a string value and a format as an argument and converts the given string according to the specified format. The TO_DATE() function retrieves a date value in “YYYY-MM-DD” format. Multiple examples were considered in this write-up to explain how the TO_DATE() function works in Postgres.