PostgreSQL Invalid Datetime Format (SQLSTATE 22007)

When PostgreSQL cannot parse a string as a valid date, time, or timestamp value, it raises ERROR: invalid input syntax for type <type>: "<value>" with SQLSTATE 22007 and condition name invalid_datetime_format. For example:

ERROR:  invalid input syntax for type date: "2024-13-01"
SQLSTATE: 22007

or, when using explicit format functions:

ERROR:  invalid value "13" for "MM"
DETAIL:  Value must be in the range 1 to 12.
SQLSTATE: 22007

What This Error Means

SQLSTATE 22007 belongs to error class 22, "Data Exception". This class covers errors where a value is syntactically valid SQL but semantically invalid for the target data type — the value simply doesn't represent a real date, time, or timestamp.

PostgreSQL raises this error during type coercion: when you cast a string literal to date, time, timetz, timestamp, or timestamptz, or when you call date/time formatting functions like to_date() or to_timestamp() with a string that does not match the supplied format mask or does not represent a calendar-valid value. The error is raised before any row is written; if the cast fails inside a larger statement, the entire statement is rolled back.

After this error, the current transaction enters an aborted state. Any subsequent statements in the same transaction block will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue ROLLBACK (or ROLLBACK TO SAVEPOINT) and start fresh.

Common Causes

1. Literal out-of-range values — Supplying a month, day, or hour that does not exist, such as month 13, day 32, or hour 25. PostgreSQL validates calendar correctness, not just syntax.
2. Format string mismatch with to_date() / to_timestamp() — Calling to_date('2024/06/15', 'YYYY-MM-DD') where the delimiter in the value (/) does not match the format mask (-), or reversing day and month fields.
3. Locale-specific or application-generated date strings — Strings like "June 2, 2024", "02-06-2024" (DD-MM-YYYY), or ISO 8601 strings with timezone offsets being cast directly to date without preprocessing.
4. Wrong column type in a COPY or bulk insert — A CSV file where a date column contains values like "N/A", "", or free-text timestamps that don't match PostgreSQL's expected input format.
5. ORM or driver sending a string instead of a parameterized date — Some older or misconfigured drivers serialize dates as strings without quoting or formatting them to match PostgreSQL's expected input syntax (YYYY-MM-DD for dates, YYYY-MM-DD HH:MI:SS for timestamps).
6. DateStyle mismatch — PostgreSQL's DateStyle setting controls how ambiguous date strings like 01/02/03 are interpreted. If an application hard-codes one style but the server is configured for another, valid-looking strings can fail or silently produce wrong values.

How to Fix invalid_datetime_format

1. Correct the value before casting. Check the actual string being passed and ensure it represents a real date:

   -- Wrong: month 13 does not exist
   SELECT '2024-13-01'::date;
   
   -- Right
   SELECT '2024-12-01'::date;
   

2. Match the format mask exactly when using to_date() or to_timestamp():

   -- Wrong: delimiter mismatch
   SELECT to_date('2024/06/15', 'YYYY-MM-DD');
   
   -- Right
   SELECT to_date('2024/06/15', 'YYYY/MM/DD');
   
   -- Right: DD-MM-YYYY input
   SELECT to_date('15-06-2024', 'DD-MM-YYYY');
   

3. Use to_timestamp() for strings with time components and non-ISO formats:

   SELECT to_timestamp('June 2, 2024 14:30', 'Month DD, YYYY HH24:MI');
   

4. Validate or sanitize before inserting in bulk. If ingesting from CSV or an external source, pre-filter rows with invalid dates:

   -- Test whether a value is castable without raising an error (PostgreSQL 14+)
   SELECT val
   FROM raw_import
   WHERE val ~ '^\d{4}-\d{2}-\d{2}$'
     AND val::date IS NOT NULL;
   

   For a safer approach on any version, use a helper function with an exception handler:

   CREATE OR REPLACE FUNCTION try_cast_date(p_text text)
   RETURNS date AS $$
   BEGIN
     RETURN p_text::date;
   EXCEPTION WHEN invalid_datetime_format OR datetime_field_overflow THEN
     RETURN NULL;
   END;
   $$ LANGUAGE plpgsql IMMUTABLE;
   
   -- Use it to skip bad rows:
   SELECT try_cast_date(raw_date_col) AS parsed_date
   FROM raw_import
   WHERE try_cast_date(raw_date_col) IS NOT NULL;
   

5. Check and align DateStyle if ambiguous formats are involved:

   -- View current setting
   SHOW DateStyle;
   
   -- Set for the session to match what the application expects
   SET DateStyle = 'ISO, MDY';
   

6. Use parameterized queries in application code rather than string-interpolating date values. Let the driver serialize the date object to the correct wire format — this avoids format mismatches entirely.

Additional Information

• SQLSTATE class 22 (Data Exception) contains many sibling conditions. Closely related codes include 22008 (datetime_field_overflow, when a value is syntactically valid but out of the supported range, e.g., year 99999), and 22P02 (invalid_text_representation, raised when a string doesn't parse as any valid value for a type).
• The invalid_datetime_format condition name can be used in EXCEPTION WHEN clauses inside PL/pgSQL functions, which is the idiomatic way to handle expected conversion failures without aborting a transaction.
• Most PostgreSQL drivers (psycopg2, psycopg3, asyncpg, JDBC, node-postgres) will surface this error as a database-level exception carrying the SQLSTATE code. ORMs like SQLAlchemy and Hibernate will typically wrap it in a driver-specific DataError with the original message preserved.
• PostgreSQL's DateStyle setting has been present since version 7. The to_date() and to_timestamp() functions have been stable since version 8. No notable behavior changes affect 22007 in recent major versions.

Frequently Asked Questions

Why does the same date string work in one PostgreSQL server but fail in another? Almost always a DateStyle difference. The DateStyle parameter controls how PostgreSQL interprets ambiguous two-digit years and day/month ordering. Check SHOW DateStyle; on both servers and align them, or switch to unambiguous ISO 8601 format (YYYY-MM-DD).

Can I catch this error without aborting my transaction? Yes, but only from within a PL/pgSQL function using a BEGIN ... EXCEPTION WHEN invalid_datetime_format THEN ... block, or with a savepoint. You cannot catch it mid-transaction from plain SQL. The recommended pattern for bulk loads is the try_cast_date helper shown above.

Why does to_date('2024-02-30', 'YYYY-MM-DD') raise 22007 but to_date('2024-02-29', 'YYYY-MM-DD') work in a leap year? PostgreSQL validates calendar correctness. February 30 never exists, so it raises invalid_datetime_format. February 29 exists only in leap years; in a non-leap year PostgreSQL raises datetime_field_overflow (22008) instead, which is a sibling error in the same class.

Is 22007 always fatal to my transaction? Yes, if raised outside a PL/pgSQL exception handler or a savepoint. Once the error is raised, your transaction is aborted and you must ROLLBACK before issuing further statements. If you need partial success (e.g., skipping bad rows), use the savepoint technique or the helper function pattern described above.