PostgreSQL 17.9
TO_DATE(string, format)- string
- String with a datetext
- format
- Format template, for example DD.MM.YYYYtext
Return value
A date (date); an error, not NULL, if a field cannot be read or the date does not exist
Examples
Date from text with a month name
PostgreSQL 17.9
SELECT TO_DATE('November 13, 1998', 'Month DD, YYYY');Date in DD.MM.YYYY format
PostgreSQL 17.9
SELECT TO_DATE('08.03.2024', 'DD.MM.YYYY');Extra text and a short year
PostgreSQL 17.9
SELECT TO_DATE('08.03.2024 garbage', 'DD.MM.YYYY') AS extra_text,
TO_DATE('8/3/24', 'DD.MM.YYYY') AS short_year;Details
The string is parsed loosely: extra text after the template is skipped, and a number can have fewer digits than in the template. That is why TO_DATE('8/3/24', 'DD.MM.YYYY') returns 0024-03-08: a two-digit year is read as is, not as 2024.
For a string in YYYY-MM-DD format it is easier to use ::date: this cast checks the whole string and does not skip extra text.