// PostgreSQL error reference

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
Error message
invalid input syntax for type timestamp: "31/12/2026 25:00"

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_date or to_timestamp that doesn't match the input.

How to fix it

  1. Send dates in ISO 8601 format, which works with every DateStyle.

    INSERT INTO events (at) VALUES ('2026-12-31 23:59:00+00');
  2. Parse other formats explicitly.

    SELECT to_timestamp('31/12/2026 23:59', 'DD/MM/YYYY HH24:MI');
  3. Clean placeholders to NULL before converting.

    SELECT NULLIF(NULLIF(trim(raw_date), ''), 'N/A')::date FROM staging;
  4. Check the session's DateStyle if 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

More data exception errors

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.

// fewer errors, faster fixes

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.