🚨 MySQL Error Guide: 'Incorrect datetime value' — Fix ERROR 1292

Overview

ERROR 1292 is the value-truncation error, most often seen with date/time and numeric conversions. The date/time form reads:

ERROR 1292 (22007): Incorrect datetime value: '2026-02-30 10:00:00' for column 'starts_at' at row 1

A closely related variant appears when a string cannot convert to a number in a comparison or write:

ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'abc'

Under strict SQL mode, MySQL rejects the value instead of coercing it. The date form is triggered by impossible dates, wrong formats, or (with NO_ZERO_DATE) the legacy '0000-00-00'.

Symptoms

• Inserts/updates rejected naming a DATE, DATETIME, or TIMESTAMP column and a row number.
• Legacy code writing '0000-00-00' or '0000-00-00 00:00:00' fails after an upgrade.
• A string date in the wrong format (MM/DD/YYYY, DD-MM-YYYY) is rejected.
• WHERE numeric_col = 'somestring' produces “Truncated incorrect DOUBLE value.”
• A valid local time is rejected around a daylight-saving spring-forward gap.

Common Root Causes

• Impossible calendar dates — '2026-02-30', '2026-13-01', or a day that does not exist in that month.
• Zero dates under strict mode — NO_ZERO_DATE/NO_ZERO_IN_DATE reject '0000-00-00' that older MySQL allowed as a placeholder.
• Wrong string format — MySQL expects 'YYYY-MM-DD HH:MM:SS'; other locales’ formats are misparsed or truncated.
• Implicit string-to-number conversion — comparing or writing a non-numeric string to a numeric column yields the DOUBLE-truncation variant.
• Timezone gaps — a wall-clock time that does not exist because of a DST transition, when using named time zones.
• Empty string into a date column — '' is not a valid date under strict mode.

Diagnostic Workflow

Confirm the column type and the active SQL mode, since the mode decides reject-vs-coerce:

SHOW CREATE TABLE events\G
SELECT @@SESSION.sql_mode;   -- look for STRICT_TRANS_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE

Validate the literal MySQL is being handed. Test the exact string in isolation:

SELECT CAST('2026-02-30 10:00:00' AS DATETIME);   -- returns NULL + warning if invalid
SHOW WARNINGS;

Format the value correctly at the source and pass a canonical string:

SELECT STR_TO_DATE('02/30/2026', '%m/%d/%Y');     -- parse per an explicit format

For the truncated-DOUBLE variant, find the offending comparison — a numeric column compared against a non-numeric string:

-- Wrong: implicit conversion of 'N/A' to a number
SELECT * FROM orders WHERE amount = 'N/A';
SHOW WARNINGS;

For timezone gaps, check whether the value falls in a DST spring-forward hole:

SELECT CONVERT_TZ('2026-03-08 02:30:00','America/New_York','UTC');  -- NULL if nonexistent

Example Root Cause Analysis

A calendar import failed on some rows:

ERROR 1292 (22007): Incorrect datetime value: '2026-02-29 09:00:00' for column 'starts_at' at row 1

The source system exported recurring events and generated Feb 29 for 2026 — but 2026 is not a leap year, so Feb 29 does not exist. SELECT CAST('2026-02-29 10:00:00' AS DATETIME) returned NULL with a warning, confirming the date itself was impossible. Strict mode correctly refused to store a non-existent date rather than coercing it to '0000-00-00' or March 1. The fix was in the exporter’s recurrence logic (clamp Feb 29 to Feb 28 in non-leap years), plus validating dates at the import boundary. Strict mode had caught a genuine data-generation bug that non-strict MySQL would have silently corrupted.

Prevention Best Practices

• Always hand MySQL canonical 'YYYY-MM-DD HH:MM:SS' strings; parse locale formats with STR_TO_DATE() explicitly.
• Keep NO_ZERO_DATE/NO_ZERO_IN_DATE and strict mode on; use NULL for “unknown,” never '0000-00-00'.
• Validate dates and numeric inputs at the application boundary and return clear errors.
• Store timestamps in UTC and convert at the edges to avoid DST-gap values.
• Never compare numeric columns against arbitrary strings; cast or validate first to avoid the truncated-DOUBLE variant.
• Test data pipelines against leap-year and month-length edge cases before loading production.

Quick Command Reference

SELECT @@SESSION.sql_mode;                           -- strict + NO_ZERO_DATE flags
SELECT CAST('2026-02-30' AS DATE); SHOW WARNINGS;    -- validate a literal
SELECT STR_TO_DATE('02/30/2026','%m/%d/%Y');         -- parse an explicit format
SELECT CONVERT_TZ('2026-03-08 02:30:00',
       'America/New_York','UTC');                    -- detect a DST gap (NULL)

Conclusion

ERROR 1292 is a truncation guard: an impossible date, a zero date, a mis-formatted string, or a non-numeric value forced into a number. The fix is to feed MySQL valid, canonically formatted values and to validate at the boundary — not to weaken sql_mode, which only re-enables the silent coercion that corrupts data. Store timestamps in UTC, parse locale formats explicitly, and treat 1292 after an upgrade as a real data-quality bug it just exposed.