SQLSTATE 22007 ERROR Class 22: Data Exception

invalid_datetime_format invalid input syntax for type date: “…” — 22007

PostgreSQL error "invalid input syntax for type date: "…"" (SQLSTATE 22007): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

A string given where a date was expected does not parse as a valid date. PostgreSQL raises SQLSTATE 22007 (invalid_datetime_format).

  • Malformed or ambiguous date text.
  • Common with unexpected formats or invalid day/month values.
  • The current DateStyle affects parsing.

What the server log shows

ERROR:  invalid input syntax for type date: "2026-13-40"

Why PostgreSQL raises this — what the manual says

Section 8.5.1 Date/Time Input:

“Date and time input is accepted in almost any reasonable format”

The date input function interprets the text according to DateStyle and validity rules. Text that is malformed or specifies an impossible date is rejected with 22007.

Common causes

  • An impossible date (month > 12, day out of range).
  • An ambiguous format misread under the current DateStyle.
  • An empty string instead of NULL.

How to fix it

  1. Use ISO format YYYY-MM-DD for unambiguous input.
  2. Set/confirm DateStyle, or parse with to_date() using an explicit format.
  3. Validate dates in the application; use NULL for missing values.

Related & next steps

Reference: PostgreSQL 18 Section 8.5 “Date/Time Types”.

Was this helpful?