PostgreSQL 22007 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22007: Invalid Datetime Format — Causes, Fixes & Prevention

PostgreSQL error 22007 (invalid_datetime_format) is thrown when the database engine cannot parse a string value into a date or time type because the input does not match the expected format. This typically occurs during type casting (e.g., '12/31/2023'::DATE) or when using formatting functions like TO_DATE() and TO_TIMESTAMP() with a mismatched format mask. It is one of the most common errors encountered in ETL pipelines and data migration projects.

---

Top 3 Causes

1. Non-Standard or Locale-Specific Date Formats

PostgreSQL defaults to ISO 8601 (YYYY-MM-DD). Feeding it a US-style (MM/DD/YYYY) or European-style (DD.MM.YYYY) string without explicit conversion will immediately trigger this error.

-- This fails
SELECT '12/31/2023'::DATE;
-- ERROR:  invalid input syntax for type date: "12/31/2023"

-- Fix: use TO_DATE() with an explicit format mask
SELECT TO_DATE('12/31/2023', 'MM/DD/YYYY');
-- Result: 2023-12-31

-- Also works for European format
SELECT TO_DATE('31.12.2023', 'DD.MM.YYYY');
-- Result: 2023-12-31

2. Out-of-Range or Nonexistent Date Values

Passing values like month 13, February 30, or hour 25 causes PostgreSQL to reject the input as an invalid datetime. This frequently surfaces during bulk data loads from legacy systems where input validation was minimal.

-- This fails (month 13 does not exist)
SELECT TO_DATE('2023-13-01', 'YYYY-MM-DD');
-- ERROR:  date/time field value out of range

-- Safe handling with exception block
CREATE OR REPLACE FUNCTION safe_to_date(p_value TEXT, p_format TEXT)
RETURNS DATE AS $$
BEGIN
    RETURN TO_DATE(p_value, p_format);
EXCEPTION
    WHEN invalid_datetime_format OR datetime_field_overflow THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_to_date('2023-13-01', 'YYYY-MM-DD'); -- Returns NULL safely
SELECT safe_to_date('2023-12-31', 'YYYY-MM-DD'); -- Returns 2023-12-31

3. Wrong Format Mask in TO_TIMESTAMP() / TO_DATE()

A common developer mistake is confusing MM (month) with MI (minutes), or using 12-hour format (HH) without the AM/PM indicator. These mask mismatches either produce wrong results silently or raise a 22007 error.

-- Wrong: MM and MI swapped (silent wrong result or error)
SELECT TO_TIMESTAMP('2023-12-31 14:30:00', 'YYYY-MI-DD HH24:MM');

-- Correct: proper mask for 24-hour timestamp
SELECT TO_TIMESTAMP('2023-12-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- Result: 2023-12-31 14:30:00+00

-- Correct: 12-hour format requires AM/PM
SELECT TO_TIMESTAMP('2023-12-31 02:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
-- Result: 2023-12-31 14:30:00+00

-- Use TO_CHAR to verify your format mask is correct
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

---

Quick Fix Solutions

• Always use TO_DATE() or TO_TIMESTAMP() with an explicit format mask instead of relying on implicit casting for non-ISO strings.
• Wrap conversions in a PL/pgSQL exception handler (like safe_to_date() above) when processing bulk or untrusted data.
• Use TO_CHAR() in reverse to verify your format mask produces the expected string pattern before applying it to TO_TIMESTAMP().

---

Prevention Tips

Standardize on ISO 8601 at the application layer. Enforce that all date/time values entering the database are pre-formatted as YYYY-MM-DD or YYYY-MM-DDTHH:MI:SS. Lock the session-level DateStyle setting to avoid surprises.

-- Lock DateStyle at database level
ALTER DATABASE mydb SET datestyle = 'ISO, YMD';

Use native date/time column types — never store dates as TEXT. Design your tables with DATE, TIMESTAMP, or TIMESTAMPTZ columns from the start. This forces validation at insert time and eliminates an entire class of format errors downstream.

-- Bad: storing dates as text
CREATE TABLE events_bad (event_date TEXT);

-- Good: native types enforce correctness
CREATE TABLE events_good (
    event_date   DATE        NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---

Related errors: 22008 datetime_field_overflow (value out of valid range), 22P02 invalid_text_representation (failed cast for non-datetime types), 42804 datatype_mismatch (type incompatibility in function arguments).

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.