What causes "ERROR 1292: Incorrect date/time value"?

Date is formatted as DD-MM-YYYY, MM/DD/YYYY, or other non-standard format instead of YYYY-MM-DD Datetime includes timezone information (e.g., "+00:00" or "Z") that MySQL DATETIME columns don't support Value falls outside the valid range for TIMESTAMP (1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC)

Is the "ERROR 1292: Incorrect date/time value" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix ERROR 1292: Incorrect date/time value in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 421 views

MySQL ERROR 1292 occurs when inserting or updating datetime values in an incorrect format. MySQL expects DATETIME columns in YYYY-MM-DD HH:MM:SS format. Use STR_TO_DATE() to convert misformatted dates or verify your date values match MySQL's strict requirements.

Quick fix

Fixes ERROR 1292: Incorrect date/time value

-- WRONG - will cause ERROR 1292
INSERT INTO events (event_date) VALUES ("15-01-2024");
INSERT INTO events (event_date) VALUES ("01/15/2024");
INSERT INTO events (event_date) VALUES ("2024-01-15T10:30:00Z");

-- CORRECT
INSERT INTO events (event_date) VALUES ("2024-01-15");
INSERT INTO events (event_date) VALUES ("2024-01-15 10:30:45");

Verify the date format in your INSERT or UPDATE statement
Use STR_TO_DATE() to convert misformatted dates
Remove timezone information from ISO 8601 timestamps
Handle TIMESTAMP range limitations

mysql — error

$-- WRONG - will cause ERROR 1292
INSERT INTO events (event_date) VALUES ("15-01-2024");
INSERT INTO events (event_date) VALUES ("01/15/2024");
INSERT INTO events (event_date) VALUES ("2024-01-15T10:30:00Z");
-- CORRECT
INSERT INTO events (event_date) VALUES ("2024-01-15");
INSERT INTO events (event_date) VALUES ("2024-01-15 10:30:45");
ERROR 1292: Incorrect date/time value

What this error means

MySQL ERROR 1292 (SQLSTATE 22007) is raised when you attempt to insert or update a DATETIME, DATE, or TIMESTAMP column with a value that doesn't match MySQL's expected format or falls outside the valid range for that data type. MySQL is strict about date/time validation and will reject improperly formatted values, values outside the valid range for the column type, or zero dates depending on your SQL mode settings. This error commonly occurs when working with application data that uses different date formats, ISO 8601 timestamps with timezone information, or legacy data with zero dates ("0000-00-00").

Typical symptoms

INSERT or UPDATE statements fail with SQLSTATE[22007] error
Date values work in one MySQL version but fail after upgrading
Dates formatted as DD-MM-YYYY or MM/DD/YYYY are rejected
ISO 8601 timestamps with trailing Z (e.g., "2024-01-15T10:30:00Z") fail in DATETIME columns
Zero dates like "0000-00-00 00:00:00" are rejected
Application crashes when trying to bulk insert or migrate data with legacy date formats

Common causes

Date is formatted as DD-MM-YYYY, MM/DD/YYYY, or other non-standard format instead of YYYY-MM-DD
Datetime includes timezone information (e.g., "+00:00" or "Z") that MySQL DATETIME columns don't support
Value falls outside the valid range for TIMESTAMP (1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC)
DATETIME string includes milliseconds or microseconds without proper formatting
MySQL strict SQL mode rejects zero dates ("0000-00-00") which were allowed in older versions
Daylight saving time transition creates non-existent times (e.g., 2016-03-13 02:30:00)
TIMESTAMP column used instead of DATETIME, causing range or timezone issues

How to fix "ERROR 1292: Incorrect date/time value"

1Verify the date format in your INSERT or UPDATE statement

Check that your date value matches MySQL's required format. MySQL only accepts:

- DATETIME: YYYY-MM-DD HH:MM:SS (e.g., "2024-01-15 10:30:45")
- DATE: YYYY-MM-DD (e.g., "2024-01-15")
- TIMESTAMP: YYYY-MM-DD HH:MM:SS (same format as DATETIME)

Common mistakes to avoid:

sql

-- WRONG - will cause ERROR 1292
INSERT INTO events (event_date) VALUES ("15-01-2024");
INSERT INTO events (event_date) VALUES ("01/15/2024");
INSERT INTO events (event_date) VALUES ("2024-01-15T10:30:00Z");

-- CORRECT
INSERT INTO events (event_date) VALUES ("2024-01-15");
INSERT INTO events (event_date) VALUES ("2024-01-15 10:30:45");

2Use STR_TO_DATE() to convert misformatted dates

If your data uses a different format, use the STR_TO_DATE() function to convert it to a format MySQL accepts:

sql

-- Convert DD/MM/YYYY format to YYYY-MM-DD
INSERT INTO events (event_date) 
VALUES (STR_TO_DATE("15/01/2024", "%d/%m/%Y"));

-- Convert MM-DD-YYYY HH:MM:SS format
INSERT INTO events (event_datetime) 
VALUES (STR_TO_DATE("01-15-2024 14:30:00", "%m-%d-%Y %H:%i:%S"));

-- Convert ISO format with T separator
INSERT INTO events (event_datetime) 
VALUES (STR_TO_DATE("2024-01-15T10:30:45", "%Y-%m-%dT%H:%i:%S"));

Common format specifiers:
- %Y = 4-digit year
- %m = Month (01-12)
- %d = Day (01-31)
- %H = Hour (00-23)
- %i = Minutes (00-59)
- %s = Seconds (00-59)
- %T = Time in HH:MM:SS format

3Remove timezone information from ISO 8601 timestamps

If you're inserting ISO 8601 timestamps with timezone data (Z or +HH:MM), remove the timezone portion:

sql

-- WRONG - ERROR 1292
INSERT INTO events (event_datetime) VALUES ("2024-01-15T10:30:00Z");
INSERT INTO events (event_datetime) VALUES ("2024-01-15T10:30:00+00:00");

-- CORRECT - Strip timezone and convert
INSERT INTO events (event_datetime) 
VALUES (STR_TO_DATE("2024-01-15T10:30:00", "%Y-%m-%dT%H:%i:%s"));

-- Or use SUBSTRING to extract the datetime portion
INSERT INTO events (event_datetime) 
VALUES (SUBSTRING("2024-01-15T10:30:00Z" FROM 1 FOR 19));

If you need to preserve timezone information, store it separately as TEXT or use application-level conversion.

4Handle TIMESTAMP range limitations

If using TIMESTAMP columns, remember they have a limited range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC.

sql

-- Check if date is within TIMESTAMP range
SELECT * FROM events WHERE event_date < "1970-01-01 00:00:01";

-- For dates outside this range, use DATETIME instead
ALTER TABLE events MODIFY COLUMN event_date DATETIME;

-- Or validate before insertion
INSERT INTO events (event_datetime) 
VALUES ("2024-01-15 10:30:45")  -- OK for both DATETIME and TIMESTAMP
VALUES ("1950-01-15 10:30:45");  -- OK for DATETIME, ERROR 1292 for TIMESTAMP

5Fix zero dates in MySQL 5.7+

Starting with MySQL 5.7, the default SQL mode rejects zero dates ("0000-00-00 00:00:00"). If you need to handle legacy data with zero dates:

Option 1: Disable strict mode (not recommended for production)

sql

SET sql_mode='';
INSERT INTO events (event_date) VALUES ("0000-00-00 00:00:00");

Option 2: Use NULL instead of zero dates (recommended)

sql

-- Change zero dates to NULL
UPDATE events SET event_date = NULL WHERE event_date = "0000-00-00";

-- Ensure column allows NULL
ALTER TABLE events MODIFY COLUMN event_date DATETIME NULL DEFAULT NULL;

Option 3: Replace zero dates with current date

sql

UPDATE events SET event_date = CURDATE() WHERE event_date = "0000-00-00";

Using NULL is the cleanest approach for handling missing or unknown dates.

6Update application code to use proper date formatting

The best long-term fix is to ensure your application always sends dates in the correct format:

JavaScript/Node.js:

javascript

// Use ISO string format and remove timezone
const date = new Date('2024-01-15T10:30:45Z');
const mysqlFormat = date.toISOString().slice(0, 19).replace('T', ' ');
// Result: "2024-01-15 10:30:45"

// Or use date-fns
import { format } from 'date-fns';
const formatted = format(date, 'yyyy-MM-dd HH:mm:ss');

PHP:

php

// Format DateTime for MySQL
$date = new DateTime('2024-01-15T10:30:45Z');
$mysqlFormat = $date->format('Y-m-d H:i:s');

Python:

python

from datetime import datetime

date = datetime.fromisoformat('2024-01-15T10:30:45+00:00')
mysql_format = date.strftime('%Y-%m-%d %H:%M:%S')

Advanced notes

Daylight Saving Time (DST) Transitions: In rare cases, ERROR 1292 occurs when inserting times that don't exist due to DST changes (e.g., 2024-03-10 02:30:00 in US timezones, which is skipped during the spring forward). If you encounter this, either store times in UTC and convert on the application side, or use a DATETIME column instead of TIMESTAMP.

Character Set Issues: Ensure your client connection uses UTF-8. Date parsing can fail silently if character encoding doesn't match. Use SET NAMES utf8mb4 before insertion.

Bulk Insert Performance: When bulk inserting large datasets with STR_TO_DATE(), use LOAD DATA INFILE with proper format specifications—it's significantly faster than row-by-row INSERT statements with conversion functions.

MySQL 8.0+ JSON Dates: If storing dates in JSON columns, ensure proper formatting before insertion, as JSON doesn't have a native date type and MySQL won't auto-convert.

Replication Compatibility: If replicating between MySQL versions with different default SQL modes, ensure both primary and replica use consistent sql_mode settings to avoid silent failures or divergence.

Frequently asked questions

What does "ERROR 1292: Incorrect date/time value" mean?: MySQL ERROR 1292 (SQLSTATE 22007) is raised when you attempt to insert or update a DATETIME, DATE, or TIMESTAMP column with a value that doesn't match MySQL's expected format or falls outside the valid range for that data type. MySQL is strict about date/time validation and will reject improperly formatted values, values outside the valid range for the column type, or zero dates depending on your SQL mode settings. This error commonly occurs when working with application data that uses different date formats, ISO 8601 timestamps with timezone information, or legacy data with zero dates ("0000-00-00").
How do I fix "ERROR 1292: Incorrect date/time value"?: MySQL ERROR 1292 occurs when inserting or updating datetime values in an incorrect format. MySQL expects DATETIME columns in YYYY-MM-DD HH:MM:SS format. Use STR_TO_DATE() to convert misformatted dates or verify your date values match MySQL's strict requirements.
What causes "ERROR 1292: Incorrect date/time value"?: Date is formatted as DD-MM-YYYY, MM/DD/YYYY, or other non-standard format instead of YYYY-MM-DD Datetime includes timezone information (e.g., "+00:00" or "Z") that MySQL DATETIME columns don't support Value falls outside the valid range for TIMESTAMP (1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC)
Is the "ERROR 1292: Incorrect date/time value" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.