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:

img

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');
img

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;
img

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;
img

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.