to_date()
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
- PostgreSQL 10
- input field range checking tightened up (commit d3cd36a1)
- PostgreSQL 7.0
- added (commit b866d2e2)
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"
References
- PostgreSQL documentation: Formatting Functions
- PostgreSQL documentation: Template Patterns for Date/Time Formatting
Useful links
- ISO 8601 - visual explanation from XKCD