to_date()

A function for converting a date string into the DATE datatype

to_date() is a system function for converting the string representation of a date into the DATE datatype using the specified format.

to_date() was added in PostgreSQL 7.0.

Usage

to_date ( text, text ) → date

to_date() converts the provided string into a DATE value using the specified format; see the PostgreSQL documentation section Template Patterns for Date/Time Formatting for details.

Note that many standard date formats can be converted to a DATE value with a simple cast; to_date() is primarily of use for converting dates in formats which are ambiguous or cannot be converted via a cast.

Change history

Examples

Basic usage example for to_date():

postgres=# SELECT to_date('25 Dec 2020', 'DD Mon YYYY');
  to_date   
------------
 2020-12-25
(1 row)

Note that many common date formats can be converted with a simple cast, e.g.:

postgres=# SELECT '7/4/2021'::DATE;
    date    
------------
 2021-04-07
(1 row)

However, if the source date is in a non-standard representation, such as the month/day/year style frequently used in the US, to_date() needs to be used:

postgres=# SELECT to_date('7/4/2021', 'MM/DD/YYYY');
  to_date   
------------
 2021-07-04
(1 row)

Attempting to convert an invalid date representation:

postgres=# SELECT to_date('99 Dec 2020', 'DD Mon YYYY');
ERROR:  date/time field value out of range: "99 Dec 2020"
  • ISO 8601 - visual explanation from XKCD

Categories

Date and time, String manipulation

See also

to_timestamp(), to_char(), to_number()