How to fix Invalid datetime format in PostgreSQL

Enable query logging or review application logs to find the exact query and the value that caused the error. Look at the full error message, which typically shows the offending string.

-- Example error message
ERROR: invalid input syntax for type timestamp: '2024/12/26'

In this case, the format '2024/12/26' does not match PostgreSQL's default timestamp format.

PostgreSQL expects specific formats by default:

- DATE: YYYY-MM-DD (e.g., 2024-12-26)
- TIMESTAMP: YYYY-MM-DD HH:MM:SS (e.g., 2024-12-26 14:30:45)
- TIMESTAMP WITH TIME ZONE: YYYY-MM-DD HH:MM:SS±HH:MM (e.g., 2024-12-26 14:30:45+00:00)

If your input data is in a different format, you need to convert it or specify the format explicitly.

Before inserting into PostgreSQL, transform the date string in your application code. For example, if you have dates in MM/DD/YYYY format, convert them to YYYY-MM-DD:

// JavaScript example
const dateString = "12/26/2024";
const [month, day, year] = dateString.split("/");
const correctFormat = `${year}-${month}-${day}`; // 2024-12-26

// Or using a date library
import { format, parse } from "date-fns";
const parsed = parse("12/26/2024", "MM/dd/yyyy", new Date());
const formatted = format(parsed, "yyyy-MM-dd"); // 2024-12-26

This is the safest approach because it keeps data validation in your application layer.

If you must convert within PostgreSQL, use TO_DATE() for dates or TO_TIMESTAMP() for timestamps with a format mask that matches your input:

-- Convert MM/DD/YYYY format to DATE
SELECT TO_DATE('12/26/2024', 'MM/DD/YYYY');
-- Result: 2024-12-26

-- Convert custom timestamp format
SELECT TO_TIMESTAMP('2024-12-26 14:30:45', 'YYYY-MM-DD HH:MI:SS');
-- Result: 2024-12-26 14:30:45+00

-- Use in INSERT statement
INSERT INTO events (event_date, created_at)
VALUES (
  TO_DATE('12/26/2024', 'MM/DD/YYYY'),
  TO_TIMESTAMP('2024-12-26 14:30:45', 'YYYY-MM-DD HH:MI:SS')
);

Common format codes:
- YYYY = 4-digit year
- MM = 2-digit month
- DD = 2-digit day
- HH or HH24 = hour (00-23)
- MI = minute
- SS = second

If your input contains ISO 8601 format with a "T" separator (e.g., "2024-12-26T14:30:45"), ensure your format mask includes the "T":

-- Correct: include T in the format
SELECT TO_TIMESTAMP('2024-12-26T14:30:45', 'YYYY-MM-DDTHH:MI:SS');
-- Result: 2024-12-26 14:30:45

-- Wrong: format does not account for T
SELECT TO_TIMESTAMP('2024-12-26T14:30:45', 'YYYY-MM-DD HH:MI:SS');
-- ERROR: invalid value 'T1' for 'HH24'

PostgreSQL 12+ enforces strict format matching, so every character must be accounted for.

Add validation in your application to catch invalid dates before they reach PostgreSQL:

-- Check for NULL or empty date values
INSERT INTO events (event_date)
VALUES (NULLIF(?, ''::text)::date); -- NULL if empty string, otherwise cast to date

-- Or in application code:
const eventDate = dateString && dateString.trim() ? dateString.trim() : null;

-- Validate date range
IF eventDate > CURRENT_DATE THEN
  -- Handle future dates if not allowed
END IF;

Preventing invalid data at the entry point is more efficient than debugging database errors later.

If your server's DateStyle setting differs from your data format, you can override it for a session or use explicit conversion:

-- Check current DateStyle
SHOW DateStyle;
-- Result might be: ISO, DMY (or MDY, YMD)

-- Set DateStyle for the current session
SET DateStyle TO 'ISO, YMD';

-- Or explicitly cast with TO_DATE to avoid ambiguity
SELECT TO_DATE('2024-12-26', 'YYYY-MM-DD');

DataStyle values:
- MDY: Month-Day-Year (common in US)
- DMY: Day-Month-Year (common in Europe)
- YMD: Year-Month-Day (ISO 8601, recommended)