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

Symptoms

A string could not be parsed as a date. PostgreSQL raises SQLSTATE 22007 (invalid_datetime_format).

• The date text isn’t in a recognized format.
• Common with ambiguous or out-of-range dates.
• Affected by DateStyle for ambiguous inputs.

What the server log shows

ERROR:  invalid input syntax for type date: "2024-02-30"

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”

Date parsing checks both format and calendar validity. Input that isn’t a recognized format, or names an impossible date (Feb 30), cannot be interpreted, so PostgreSQL reports 22007.

Common causes

• A malformed or non-existent calendar date.
• An ambiguous format under the current DateStyle.
• Locale/format mismatch between the app and the server.

How to fix it

1. Use ISO 8601: '2024-02-28'.
2. Set DateStyle appropriately for ambiguous inputs.
3. Use to_date(text, format) to parse non-standard formats explicitly.

Related & next steps

• SQLSTATE 22007 — Invalid Datetime Format (full reference)
• Message: invalid input syntax for type timestamp
• Glossary: date

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