Dates and times

TO_DATE

Converts text to a date using the given format.

Syntax

TO_DATE(<text>, '<format>')

The mirror of TO_CHAR: it reads a date from text written differently from what PostgreSQL expects — for example 13.09.2026. The patterns are the same: DD — day, MM — month, YYYY — year, Month — the month name.

A day-first date

SELECT TO_DATE('13.09.2026', 'DD.MM.YYYY') AS day;

day
2026-09-13

The American format

SELECT TO_DATE('09/13/2026', 'MM/DD/YYYY') AS day;

day
2026-09-13

The same day: the month and the day are simply in a different order.

With a month name

SELECT TO_DATE('13 September 2026', 'DD Month YYYY') AS day;

day
2026-09-13

Watch out

The format has to match the text in meaning: TO_DATE('13.09.2026', 'MM.DD.YYYY') throws date/time field value out of range, because there's no month 13.

Separators aren't checked strictly: TO_DATE('13.09.2026', 'DD-MM-YYYY') works too. So TO_DATE won't help you check whether text really is in the expected format.

Is the text already in the usual 2026-09-13 form? Then you don't need TO_DATE — '2026-09-13'::date is enough. A date with a time is read by TO_TIMESTAMP, but it returns timestamp with time zone: if you need a plain one, add ::timestamp.

Related functions

TO_CHAR CAST EXTRACT