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.