Back to list PostgreSQL How to Convert a String to a Date in PostgreSQL Database: PostgreSQL Operators:TO_DATE() Problem: You’d like to convert a string containing a date to the PostgreSQL date data type. Let’s convert a date string to the date data type instead of its current text data type. Solution: Here’s Example 1 of how to use the TO_DATE() function. This is the query you would write: SELECT TO_DATE('20020304', 'YYYYMMDD') AS new_date; And here’s the result of the query: new_date 2002-03-04 Let’s look at Example 2 of the TO_DATE() function. Notice the slightly different date format: SELECT TO_DATE('2015/06/07', 'YYYY/MM/DD') AS new_date; Here’s the result of the query: new_date 2015-06-07 Discussion: Use the function TO_DATE() to convert a text value containing a date to the date data type. This function takes two arguments: A date value. This can be a string (a text value) or a text column containing date information. In our example, we used the strings '20020304' and '2015/06/07'. The input date format. In our example, we used 'YYYYMMDD' and 'YYYY/MM/DD'. Notice that the input format is a string. The input format decides how PostgreSQL will process the characters in the string where the date is stored. The specifier ‘YYYY’ as the first four characters indicates that these represent a 4-digit year. Next, ‘MM’ represents a 2-digit month and ‘DD’ a 2-digit day. You can find a list of all specifiers in the PostgreSQL documentation. In Example 1, the string date as ‘20020304’ was converted to the date '2002-03-04' (a date data type. In Example 2, the string '2015/06/07' was converted to the date '2015-06-07'. In the second example, we used the slash / between date parts to correctly convert from a string to the date data type. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Extract the Week Number from a Date in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.