PostgreSQL Error 22007: Invalid Datetime Format

PostgreSQL SQLSTATE 22007 is invalid_datetime_format. It means the supplied text cannot be interpreted using the expected date/time format. Capture the full error message and the exact input value; PostgreSQL assigns 22007 in its error-code appendix.

Check the target type and session DateStyle

Confirm whether the target is date, timestamp, or timestamp with time zone, and inspect the session’s input-order setting:

SHOW DateStyle;

DateStyle can make numeric dates such as 04/05/2026 ambiguous: it may mean April 5 or May 4 depending on whether input order is MDY or DMY. PostgreSQL documents the accepted date/time input formats and DateStyle behavior.

Prefer unambiguous ISO values

For application parameters and SQL literals, prefer ISO year-month-day order. Specify the intended type where it helps readability:

SELECT DATE '2026-09-27';
SELECT TIMESTAMP '2026-09-27 14:30:00';
SELECT TIMESTAMPTZ '2026-09-27 14:30:00+08:00';

Pass date/time parameters using the driver’s typed parameter support rather than concatenating localized display strings into SQL. For timestamps, decide whether the value represents local wall-clock time or a specific instant with a time zone.

Parse a fixed legacy format explicitly

If an import source uses a documented non-ISO format, parse it using the matching template instead of relying on DateStyle:

SELECT to_date('27/09/2026', 'FXDD/MM/YYYY');

FX must be the first item in the template. It tightens spacing and separator matching, but a template separator still matches any one non-letter, non-digit character; FXDD/MM/YYYY does not require the input to use / specifically. If the source requires literal slashes, validate the raw string shape before parsing, for example with raw_date ~ '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'. Then validate the parsed date against the source and application rules. PostgreSQL’s to_date and to_timestamp template rules describe the FX behavior. For standard ISO values, a cast is usually simpler.

For PostgreSQL 16 and later, pg_input_is_valid can check whether a value is accepted by the date input function before a load or conversion:

SELECT raw_date,
       (pg_input_error_info(raw_date, 'date')).message AS input_error
FROM staging_events
WHERE NOT pg_input_is_valid(raw_date, 'date');

These functions only avoid aborting the statement for data types whose input functions support soft errors. See PostgreSQL’s data validity checking functions.

Distinguish related conversion errors

• 22007 (invalid_datetime_format): the date/time text does not match an accepted input format.
• 22008 (datetime_field_overflow): a date/time field value is out of range; see Error 22008 troubleshooting.
• 22P02 (invalid_text_representation): text cannot be parsed as a requested type; see Error 22P02.
• 42804 (datatype_mismatch): the expression types are incompatible; see Error 42804.

For other SQLSTATE guides, browse PostgreSQL Error Troubleshooting.