PostgreSQL 22007: invalid datetime format
A string couldn't be read as a date, time or timestamp: an unknown format, impossible field, or ambiguous day/month order.
- SQLSTATE
22007- Name
invalid_datetime_format- Category
- Data Exception
What it means
PostgreSQL parses date and time input in ISO 8601 (2026-12-31 23:59:00) and several other formats, using the DateStyle setting to decide whether 01/02/2026 is January 2 or February 1. Text it can't parse at all fails with 22007; text that parses but has an out-of-range field (month 13, hour 25) usually fails with 22008 instead.
to_date and to_timestamp with an explicit format string avoid guessing.
Common causes
- A date in a regional format that doesn't match
DateStyle. - Empty strings or placeholder text such as
'N/A'in a date column of an import file. - Extra text, such as a weekday name or a time zone abbreviation PostgreSQL doesn't know.
- A format string in
to_dateorto_timestampthat doesn't match the input.
How to fix it
-
Send dates in ISO 8601 format, which works with every
DateStyle.INSERT INTO events (at) VALUES ('2026-12-31 23:59:00+00'); -
Parse other formats explicitly.
SELECT to_timestamp('31/12/2026 23:59', 'DD/MM/YYYY HH24:MI'); -
Clean placeholders to
NULLbefore converting.SELECT NULLIF(NULLIF(trim(raw_date), ''), 'N/A')::date FROM staging;
-
Check the session's
DateStyleif numeric dates are read in the wrong order.SHOW DateStyle; SET DateStyle = 'ISO, DMY';
Reproduce it safely
SELECT 'not a date'::date; -- ERROR: invalid input syntax for type date: "not a date"
Related errors
22008date/time field value out of range22P02invalid input syntax for type241SQL Server: Conversion failed for date and/or time1292MySQL: Truncated incorrect valueORA-01861Oracle: literal does not match format stringORA-01843Oracle: not a valid month
More data exception errors
22021invalid byte sequence for encoding22012division by zero22023invalid parameter value22003numeric value out of range22001value too long for type
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.