PostgreSQL TO_DATE Function

Summary: In this tutorial, you’ll learn how to convert a string to a date based on a format using the PostgreSQL TO_DATE function.

PostgreSQL TO_DATE Function Overview #

The TO_DATE function allows you to convert a string (char, varchar, text) to a date based on a specified format.

The function can be helpful when you import external data, such as CSV files, into the database and need to convert text to data for storing.

Here’s the syntax of the TO_DATE function:

TO_DATE (text, format)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• text is the string you want to convert to a date.
• format is the format of the date.

The TO_DATE function returns a value of the DATE represented by a string (text) in a specific format.

If the TO_DATE function cannot convert the string to a date, it’ll issue an error.

The function returns NULL if either argument text or format is NULL.

Basic TO_DATE Function Example #

The following example uses the TO_DATE function to convert the string 31-12-2025 to a date:

SELECT TO_DATE('31-12-2025', 'DD-MM-YYYY') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
------------
 2025-12-31Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the string '31-12-2025' represents a date in the format 'DD-MM-YYYY'.

So, to convert it to a date, we need to provide the exact format.

Parsing a Date Without Leading Zeros #

The following example uses the TO_DATE function to parse a string '3/19/25' to a date:

SELECT TO_DATE('3/19/25', 'MM/DD/YY') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
------------
 2025-03-19Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Invalid Date String Example #

The following example issues an error because the TO_DATE function cannot convert an invalid date string to a date:

SELECT TO_DATE('2025/02/29', 'YYYY/MM/DD');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

ERROR:  date/time field value out of range: "2025/02/29"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You have learned the format for day (D), month (M), and year (Y) so far. PostgreSQL offers many other date format patterns you can use for formatting a date.

Summary #

• Use the TO_DATE function to convert a string to a date based on a format.

Quiz #