invalid input syntax for type date: “…”

SQLSTATE 22007 condition invalid_datetime_format class 22 — Data Exception severity ERROR

Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.

Last reviewed 30 May 2025 · Reproduced live with the SQL on this page.

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

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

Related & next steps

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