How to Fix MySQL Error 1292: Incorrect Datetime Value

ERROR 1292 (22007): Incorrect datetime value: 'value' for column 'col' at row N is raised by MySQL when a value being stored in a DATETIME, DATE, TIMESTAMP, or TIME column cannot be parsed as a valid date/time. It also appears as a truncated warning: Warning 1292: Truncated incorrect datetime value: 'value'.

Impact

In strict SQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES — the default in MySQL 5.7.5+), the statement is aborted and rolled back. In non-strict mode, the invalid value is stored as the zero date (0000-00-00 00:00:00) and a warning is issued. Applications migrated from non-strict to strict environments frequently surface this error, because they relied on zero-date fallback behavior.

Common Causes

1. Zero date (0000-00-00 or 0000-00-00 00:00:00) stored or inserted in strict mode — prohibited unless ALLOW_INVALID_DATES or NO_ZERO_DATE is disabled in sql_mode
2. Invalid date format: sending "2024/01/15" (slashes) or "15-01-2024" (day-first) instead of "2024-01-15" (ISO 8601)
3. Daylight saving time gap: a TIMESTAMP value like 2024-03-10 02:30:00 in a North American timezone — that time does not exist because clocks spring forward from 2:00 to 3:00
4. String passed where a date is expected: "never", "N/A", "" (empty string), or NULL being cast to a DATE column
5. sql_mode difference between environments: development has NO_ZERO_DATE disabled, production has it enabled via STRICT_TRANS_TABLES
6. ORM or driver passing Python datetime.date as a string in the wrong locale format
7. Month 0 or day 0 in data that uses 0 as a sentinel for "no date": 2024-00-01 or 2024-01-00
8. Milliseconds in wrong format: "2024-01-15 10:30:00.999" is valid, but "2024-01-15T10:30:00.999Z" (ISO 8601 with T and Z) requires parsing by the application, not MySQL

Troubleshooting and Resolution Steps

1. Check current sql_mode:

   SELECT @@SESSION.sql_mode;
   SELECT @@GLOBAL.sql_mode;
   

   Key modes that affect date handling:

   • STRICT_TRANS_TABLES / STRICT_ALL_TABLES — converts warnings to errors for invalid data
   • NO_ZERO_DATE — disallows 0000-00-00
   • NO_ZERO_IN_DATE — disallows 2024-00-01 or 2024-01-00
   • ALLOW_INVALID_DATES — permits month > 12, day > 31, etc. (for relaxed calendar storage)

2. Find the invalid values before inserting. For bulk operations, validate the data first:

   -- STR_TO_DATE returns NULL for invalid formats
   SELECT raw_date, STR_TO_DATE(raw_date, '%Y-%m-%d') AS parsed
   FROM staging_table
   WHERE STR_TO_DATE(raw_date, '%Y-%m-%d') IS NULL
     AND raw_date IS NOT NULL;
   

3. Use STR_TO_DATE() to parse non-standard date strings:

   -- Slash-separated dates
   SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');   -- 2024-01-15
   
   -- US format
   SELECT STR_TO_DATE('01/15/2024', '%m/%d/%Y');   -- 2024-01-15
   
   -- With time
   SELECT STR_TO_DATE('2024-01-15 10:30:00', '%Y-%m-%d %H:%i:%s');
   

4. Handle zero dates in legacy data:

   -- Convert zero dates to NULL during migration
   UPDATE legacy_table
   SET created_at = NULL
   WHERE created_at = '0000-00-00 00:00:00';
   
   -- Or use NULLIF in a query
   SELECT NULLIF(created_at, '0000-00-00 00:00:00') AS created_at FROM t;
   

   To temporarily allow zero dates for an import:

   SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_DATE', '');
   -- import...
   SET SESSION sql_mode = @@GLOBAL.sql_mode;  -- restore
   

5. Fix timezone gap issues with TIMESTAMP columns. Times that fall in a DST gap (clocks spring forward) do not exist in local time:

   -- Check server timezone
   SHOW VARIABLES LIKE 'time_zone';
   SELECT @@global.time_zone, @@session.time_zone;
   
   -- Use UTC for TIMESTAMP storage to avoid DST issues entirely
   SET GLOBAL time_zone = '+00:00';
   SET SESSION time_zone = '+00:00';
   

   Or store datetime as DATETIME (not TIMESTAMP) — DATETIME stores exactly what you give it without timezone conversion.

6. Align sql_mode across environments to prevent behavior differences:

   -- In my.cnf — same value in all environments
   [mysqld]
   sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
   

   Run SELECT @@GLOBAL.sql_mode in all environments and compare. Add the full value to my.cnf to make it explicit and version-controllable.

7. Use CAST() or CONVERT() to validate a value before storing:

   -- Test whether a string is a valid DATETIME
   SELECT CAST('2024-01-15' AS DATETIME);
   -- Returns '2024-01-15 00:00:00' if valid, or an error if not
   

8. Fix ORM or driver formatting. Python pymysql / mysqlclient automatically converts datetime.datetime objects to the correct format. Problems arise when strings are passed instead:

   # Wrong: sending a string in locale-specific format
   cursor.execute("INSERT INTO events (ts) VALUES (%s)", ("01/15/2024",))
   
   # Right: use a datetime object
   from datetime import datetime
   cursor.execute("INSERT INTO events (ts) VALUES (%s)",
                  (datetime(2024, 1, 15),))
   
   # Right: explicit ISO 8601 string
   cursor.execute("INSERT INTO events (ts) VALUES (%s)", ("2024-01-15 00:00:00",))
   

9. Relax validation for legacy applications (temporary workaround):

   -- Remove strict date-related modes for the session
   SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
   

   This is appropriate only during data migration from a legacy system. Restore strict mode once migration is complete.

Additional Information

• TIMESTAMP stores values as UTC internally and converts to/from the session time zone on read/write. A timestamp value that is valid in UTC but falls in a DST gap in the session timezone will error. DATETIME does not do timezone conversion and is safer for storing calendar events in local time.
• The range of TIMESTAMP is 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (Y2K38). Values outside this range cause error 1292. Use DATETIME for dates beyond 2038.
• MySQL accepts '2024-1-5' (without zero-padding) as '2024-01-05' — but '2024-13-01' (month 13) is always invalid in strict mode.
• WARNING 1292 in non-strict mode stores 0000-00-00 00:00:00 silently. Check SHOW WARNINGS after bulk inserts in non-strict environments to catch data quality problems that will break in strict mode.

Frequently Asked Questions

Q: This worked in development but fails in production. The data is the same. Why? A: sql_mode almost certainly differs. Run SELECT @@GLOBAL.sql_mode in both environments and compare. Production typically has STRICT_TRANS_TABLES and NO_ZERO_DATE enabled; development may not.

Q: How do I find all zero dates in an existing table before migrating to strict mode? A: SELECT * FROM t WHERE date_col = '0000-00-00' OR date_col IS NULL (or = '0000-00-00 00:00:00' for DATETIME). Fix or null these out before enabling strict mode.

Q: My application inserts None / null for optional dates. Is that valid? A: Yes, if the column allows NULL (NOT NULL not set). The error occurs when a non-nullable column receives NULL or when the column has a DEFAULT '0000-00-00' that is invalid in strict mode. Change the default to NULL and mark the column nullable, or provide a valid non-zero default.

Q: Is '2024-02-29' valid? A: Yes, in 2024 (a leap year). '2023-02-29' is not valid (2023 is not a leap year) and raises error 1292 in strict mode.

Q: How do I store "no date" safely in MySQL? A: Use NULL (column must be nullable). Avoid zero dates — they are non-portable, cause confusion, and are rejected in strict mode. COALESCE(date_col, 'N/A') converts NULL to a display string at the query level without storing an invalid value.