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.

Example:

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('20230304', '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('2023/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 '20230304' and '2023/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 '20230304' was converted to the date value of 2023-03-04 (a date data type). In Example 2, the string '2023/06/07' was converted to the date value of 2023-06-07. In the second example, we used the slash / between date parts to correctly convert from a string to the date data type.