# 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. The TO_DATE() function retrieves a date value in “Y…

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-to_date-function-convert-string-to-date/)