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

MySQLINTERMEDIATEMEDIUM

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 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").

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')

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

What this error means

Typical symptoms

Common causes

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

Advanced notes

Related errors

Official resources & further reading