MySQL error 1292: Truncated incorrect value
MySQL couldn't convert a value to the needed type, often a malformed date or a string compared with a number. Strict mode turns it into an error.
- Error number
1292- Name
ER_TRUNCATED_WRONG_VALUE- SQLSTATE
22007- Category
- Data values
What it means
1292 says a value had to be cut off or replaced while converting it, for example Truncated incorrect DOUBLE value: '12abc' or Incorrect datetime value: '2024-02-30'. In a SELECT it is usually a warning; in INSERT, UPDATE and DELETE under strict mode it becomes an error.
Common causes
- Dates in the wrong format (
31/12/2024) or impossible dates (2024-02-30). - Zero dates
0000-00-00withNO_ZERO_DATEin the SQL mode (the default). - Comparing a string column to a number, for example
WHERE code = 123when some codes contain letters. - Updating a numeric column from a string that isn't a number.
How to fix it
-
Convert dates explicitly with
STR_TO_DATE.INSERT INTO events (happened_at) VALUES (STR_TO_DATE('31/12/2024', '%d/%m/%Y')); -
Compare strings with strings.
SELECT * FROM products WHERE code = '123';
-
Replace zero dates with
NULLbefore tightening the SQL mode or migrating.UPDATE events SET happened_at = NULL WHERE happened_at = '0000-00-00';
Related errors
1366Incorrect value for column1264Out of range value for column241SQL Server: Conversion failed for date and/or time22007PostgreSQL: invalid datetime formatORA-01861Oracle: literal does not match format string
More data values errors
1364Field doesn't have a default value1406Data too long for column1265Data truncated for column1067Invalid default value
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.