PostgreSQL: date/time field value out of range

What it means

PostgreSQL read your string as a date or time, and one of its fields is outside what that field can be. The message quotes the whole string:

ERROR:  date/time field value out of range: "2026-02-30"

Either the date doesn’t exist (30 February, month 13, minute 61), or PostgreSQL read the fields in a different order than you meant. When it suspects the order, it adds a hint:

ERROR:  date/time field value out of range: "25/12/2026"
HINT:  Perhaps you need a different "DateStyle" setting.

The DateStyle setting decides how ambiguous dates like 03/04/2026 are read. Its default is ISO, MDY (month first, the US order), so 25/12/2026 means month 25. ISO dates (2026-12-25) are read the same way whatever the setting.

The same code, 22008, covers timestamp out of range (a value past the type’s limits, which run from 4713 BC to 294276 AD for timestamp) and date field value out of range from make_date().

Common causes

Day-first dates (25/12/2026) from a European or British spreadsheet, a CSV or a form, read with the default month-first DateStyle.
Impossible dates generated by code: adding a month to 31 January by changing the month number, 29 February in a non-leap year, a day computed as 0.
MySQL “zero dates” (0000-00-00, 0000-00-00 00:00:00) in data moved from MySQL. PostgreSQL has no year 0 and no zero date.
Times past midnight written as 24:30 or 25:00, from systems that count shifts past midnight.
Epoch values used as strings, or milliseconds where seconds were expected, which land thousands of years out.

How to fix it

Send ISO 8601 dates

YYYY-MM-DD and YYYY-MM-DD HH:MM:SS are unambiguous under every DateStyle. Most drivers send dates this way when you bind a date object rather than a string.

Parse other formats explicitly

SELECT to_date('25/12/2026', 'DD/MM/YYYY');               -- 2026-12-25
SELECT to_timestamp('25/12/2026 14:30', 'DD/MM/YYYY HH24:MI');

Or change how this session reads ambiguous dates: SET datestyle = 'ISO, DMY';. For a role or a database, ALTER ROLE … SET datestyle = 'ISO, DMY'. Prefer to_date in imports, so the code says which format it expects.

Find the bad values first

In a staging table of text, on PostgreSQL 16 and later:

SELECT id, raw_date FROM staging WHERE NOT pg_input_is_valid(raw_date, 'date');

Turn zero dates into NULL

INSERT INTO orders (id, shipped_at)
SELECT id, NULLIF(shipped_at, '0000-00-00 00:00:00')::timestamp FROM staging;

Do date arithmetic with intervals

date '2026-01-31' + interval '1 month' gives 2026-02-28 00:00:00, the last day of February, instead of an impossible 31 February. make_date(y, m, d) fails loudly on a bad combination, which is better than storing the wrong day.

Convert epoch values with to_timestamp

to_timestamp(1790845200) for seconds, to_timestamp(ms / 1000.0) for milliseconds.

Reproduce it

On PostgreSQL 18.6, with the default DateStyle of ISO, MDY:

SELECT '2026-02-30'::date;
SELECT '13/25/2026'::date;
SELECT '25/12/2026'::date;
SELECT '2026-10-11 25:00'::timestamp;
SELECT '0000-00-00'::date;

ERROR:  date/time field value out of range: "2026-02-30"
LINE 1: SELECT '2026-02-30'::date;
               ^
ERROR:  date/time field value out of range: "13/25/2026"
LINE 1: SELECT '13/25/2026'::date;
               ^
HINT:  Perhaps you need a different "DateStyle" setting.
ERROR:  date/time field value out of range: "25/12/2026"
LINE 1: SELECT '25/12/2026'::date;
               ^
HINT:  Perhaps you need a different "DateStyle" setting.
ERROR:  date/time field value out of range: "2026-10-11 25:00"
LINE 1: SELECT '2026-10-11 25:00'::timestamp;
               ^
ERROR:  date/time field value out of range: "0000-00-00"
LINE 1: SELECT '0000-00-00'::date;
               ^

After SET datestyle = 'ISO, DMY', '25/12/2026'::date returned 2026-12-25, and so did to_date('25/12/2026', 'DD/MM/YYYY') under the default setting. to_date('2026-02-30', 'YYYY-MM-DD') failed with the same message, and make_date(2026, 2, 30) with date field value out of range: 2026-02-30. '294277-01-01'::timestamp gave timestamp out of range: "294277-01-01". to_timestamp(1790845200000) (milliseconds passed as seconds) didn’t fail: it returned the year 58719.

With \set VERBOSITY verbose, psql shows the code: ERROR: 22008: date/time field value out of range: "2026-02-30". PostgreSQL 14.24 gives the same messages, with the hint spelled "datestyle" in lower case.

In Inlet

When a statement fails, Inlet shows the error at the position the server reports, with PostgreSQL’s hint about DateStyle when it sends one. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

date/time field value out of range