📋

Description

Error 22007, 'invalid datetime format', occurs when PostgreSQL receives a string value that it cannot parse into a valid date, time, or timestamp. This typically happens during data insertion, updates, or explicit conversions when the input string does not conform to expected date/time patterns.

💬

Error Message

invalid datetime format

🔍

Known Causes

3 known causes

⚠️

Input String Format Mismatch

The date/time string provided does not match any of the standard formats recognized by PostgreSQL's 'datestyle' setting or the expected format for the target column.

⚠️

Incorrect Format Specifier

When using conversion functions like TO_DATE() or TO_TIMESTAMP(), the format string supplied does not accurately describe the actual format of the input date/time string.

⚠️

Invalid Date/Time Components

The input string contains values that are not logically valid for date or time components (e.g., 'February 30', '25:00:00').

🛠️

Solutions

4 solutions available

1. Use Correct Date Format easy

PostgreSQL expects ISO format YYYY-MM-DD

1

Use standard format

-- Wrong:
INSERT INTO events (event_date) VALUES ('01/15/2024');

-- Right:
INSERT INTO events (event_date) VALUES ('2024-01-15');

2

For timestamp

INSERT INTO events (event_time) VALUES ('2024-01-15 14:30:00');

2. Use TO_DATE/TO_TIMESTAMP easy

Convert non-standard formats

1

Convert date string

INSERT INTO events (event_date)
VALUES (TO_DATE('01/15/2024', 'MM/DD/YYYY'));

2

Convert timestamp string

INSERT INTO events (event_time)
VALUES (TO_TIMESTAMP('15-Jan-2024 2:30 PM', 'DD-Mon-YYYY HH:MI PM'));

3

Common format patterns

-- YYYY = 4-digit year
-- MM = month (01-12)
-- DD = day (01-31)
-- HH24 = hour 24h
-- HH = hour 12h
-- MI = minutes
-- SS = seconds
-- Mon = month name abbrev
-- Month = full month name

3. Set DateStyle medium

Change session date interpretation

1

Check current DateStyle

SHOW DateStyle;

2

Set for session

SET DateStyle = 'ISO, MDY';  -- For MM/DD/YYYY input
SET DateStyle = 'ISO, DMY';  -- For DD/MM/YYYY input

4. Format in Application easy

Convert to correct format before sending

1

Python

from datetime import datetime
date_str = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

2

JavaScript

const date = new Date();
const pgDate = date.toISOString();  // '2024-01-15T14:30:00.000Z'

🔗

Related Errors

5 related errors

PostgreSQL Error 22007: Invalid Datetime Format