EC
22007
PostgreSQLERRORCommonData ExceptionHIGH confidence

invalid input syntax for type date

What this means

Postgres attempted to parse a string as a date, time, or timestamp and could not match it against any recognised format. The type input function (date_in, timestamp_in, etc.) rejects the string before any storage occurs.

Why it happens
  1. 1Date string formatted for a different locale (e.g., DD/MM/YYYY when Postgres expects YYYY-MM-DD)
  2. 2Two-digit year ambiguity or missing year in the input string
  3. 3Timestamp string contains a timezone offset in an unexpected format
  4. 4Passing a text value from an application that formats dates differently from ISO 8601
How to reproduce

A date string in a non-ISO format is cast to DATE.

trigger — this will error
trigger — this will error
SELECT '25/12/2024'::DATE;

expected output

ERROR:  invalid input syntax for type date: "25/12/2024"
LINE 1: SELECT '25/12/2024'::DATE;

Fix 1

Use ISO 8601 format (YYYY-MM-DD)

WHEN When you control the input format.

Use ISO 8601 format (YYYY-MM-DD)
SELECT '2024-12-25'::DATE; -- ISO 8601 always works regardless of DateStyle

SELECT '2024-12-25 14:30:00'::TIMESTAMP;
SELECT '2024-12-25T14:30:00Z'::TIMESTAMPTZ;

Why this works

Postgres date parsing in date_in() always accepts ISO 8601 format regardless of the DateStyle setting. ISO format is unambiguous and is the safest choice for application-generated date strings.

Fix 2

Use TO_DATE / TO_TIMESTAMP with explicit format

WHEN When the input format is fixed but non-ISO (e.g., from a legacy system).

Use TO_DATE / TO_TIMESTAMP with explicit format
SELECT TO_DATE('25/12/2024', 'DD/MM/YYYY');

SELECT TO_TIMESTAMP('25-12-2024 14:30', 'DD-MM-YYYY HH24:MI');

Why this works

TO_DATE and TO_TIMESTAMP parse strings according to an explicit format template, bypassing the automatic format detection in date_in(). The format template uses Postgres format codes (DD, MM, YYYY, HH24, MI, SS) which are unambiguous.

What not to do

✕ Set DateStyle to 'SQL, DMY' globally to make European dates parse

Makes date string interpretation session-dependent and fragile; ISO 8601 is unambiguous and universally accepted.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors