TO_DATE in PostgreSQL: parsing a string into a date by template

When a date arrives as a string that is not in ISO format, a plain '15/03/2024'::date cast will either fail or read it differently than you expect. The TO_DATE function parses a string against an explicit template you write yourself, which makes the result predictable. You meet it most often when importing CSVs, running migrations, or parsing hand-made exports where the order of day, month and year is known in advance and has to be pinned down right in the query.

Basic syntax

TO_DATE takes two arguments: the input string and a format template that describes how that string is laid out.

-- Input string, then the format template that describes it
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d;
-- result: 2024-03-15

The template is built from field codes: YYYY is a four-digit year, MM the month, DD the day, Mon a short month name, Month the full one. Literals like dashes and slashes are written as-is:

SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY')   AS eu_style,
       TO_DATE('March 15, 2024', 'Month DD, YYYY') AS verbose,
       TO_DATE('20240315', 'YYYYMMDD')        AS compact;

Why not just ::date

The ::date cast relies on the server's datestyle setting and mostly understands ISO-like strings. For ambiguous formats it is dangerous:

-- Ambiguous: is this March 4 or April 3?
SELECT '03/04/2024'::date;        -- depends on server datestyle
SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS as_us;  -- explicit: March 4
SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS as_eu;  -- explicit: April 3

With TO_DATE the intent is encoded in the query itself and does not depend on server config. A practical case is loading user birth dates that arrived in European format:

-- Imagine a staging table with raw text dates
SELECT id, email, TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_day
FROM users_staging;

Advantages of an explicit template:

• the result does not depend on the server datestyle or locale;
• the query is self-documenting: you can see which format is expected;
• it works the same for US, EU and any custom layout.

The main gotcha: out-of-range fields depend on the version

The crucial detail about TO_DATE is that its strictness depends on the PostgreSQL version, and this is exactly where migrations break. The comments below show the old behaviour before PostgreSQL 16: month 13 and day 32 did not raise an error, they "rolled forward" instead (month 13 became January of the next year, day 32 the first of the next month). That silent promotion of an invalid date into a valid one was the classic source of hard-to-find bugs.

-- Before PostgreSQL 16: 13 is not a month, but TO_DATE rolled it over
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD');  -- pre-16: 2025-01-01; 16+: errors

-- Same story for day 32
SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD');  -- pre-16: 2024-02-01; 16+: errors

-- Separators in data and template can still mismatch silently
SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD');  -- still parses on any version

Starting with PostgreSQL 16 the behaviour changed: the first two queries no longer overflow the extra, they raise an error instead — date/time field value out of range for month 13 and date out of range for day 32. The 2025-01-01 and 2024-02-01 results in the comments only appear on PostgreSQL 15 and older. The third query, however, is lenient on every version: a separator mismatch between data and template (/ vs -) still passes without complaint. So if you need guaranteed strictness across versions, do not rely on the built-in range check — verify the result by formatting it back with TO_CHAR:

-- Reject rows where round-trip does not match the input
SELECT raw_signup
FROM users_staging
WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;

Two-digit years

The YY code triggers a windowing rule: PostgreSQL completes a two-digit year relative to the current one, which is almost never what you want for historical dates.

-- YY uses a sliding window around the current year
SELECT TO_DATE('15-03-49', 'DD-MM-YY');  -- 2049, maybe fine
SELECT TO_DATE('15-03-99', 'DD-MM-YY');  -- 1999, sliding-window guess

-- Force the century explicitly with RR or just demand 4 digits
SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY');  -- no guessing

Tip: require YYYY where you can and normalize the source data to four-digit years to remove the guessing entirely.

TO_DATE vs TO_TIMESTAMP

TO_DATE discards the time part: the result is always of type date. If the string has hours, minutes and seconds, reach for TO_TIMESTAMP, otherwise you silently lose precision.

-- TO_DATE drops the time portion entirely
SELECT TO_DATE('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');  -- 2024-03-15

-- TO_TIMESTAMP keeps hours, minutes, seconds
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00

Differences across databases:

• MySQL has no TO_DATE; use STR_TO_DATE('15/03/2024', '%d/%m/%Y') with percent codes, and in strict mode it returns NULL on an invalid date.
• ClickHouse offers parseDateTimeBestEffort and a strict parseDateTime with a Java-style template; use the OrNull suffix for safety.

-- MySQL equivalent uses percent-style format codes
SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;

When you move date parsing between PostgreSQL, MySQL and ClickHouse, the real danger is not the TO_DATE syntax but the difference in how each engine treats invalid values. PostgreSQL before 16 silently rolls month 13 and day 32 forward, PostgreSQL 16+ raises an error on them, MySQL in strict mode (STR_TO_DATE) returns NULL, and ClickHouse depends on whether you picked parseDateTime or the OrNull variant. So before migrating, run the same small table of edge cases through every engine: NULL, empty string, month 13, day 32, a two-digit year and mismatched separators. On the happy path of clean ISO dates all engines agree; they diverge precisely on those rows.

Note separately that TO_DATE(column, ...) in a WHERE clause is a function over the column, and it blocks a plain index on that column. If you filter on the parsed date over large tables, parse the strings once at load time into a date column and build the index on that, instead of calling TO_DATE in every query.

Bottom line: use TO_DATE with an explicit template for ambiguous formats, do not trust ::date on non-ISO strings, remember that out-of-range fields are silently rolled over before PostgreSQL 16 but raise an error on 16+, mind the two-digit-year trap, and reach for TO_TIMESTAMP when the value carries a time.