How to Convert a String to a Date in PostgreSQL

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:

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:

Discussion:

Use the function TO_DATE() to convert a text value containing a date to the date data type. This function takes two arguments:

1. 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'.
2. 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.