// MySQL error reference

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
Error message
Truncated incorrect %s value: '%s'

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-00 with NO_ZERO_DATE in the SQL mode (the default).
  • Comparing a string column to a number, for example WHERE code = 123 when some codes contain letters.
  • Updating a numeric column from a string that isn't a number.

How to fix it

  1. Convert dates explicitly with STR_TO_DATE.

    INSERT INTO events (happened_at) VALUES (STR_TO_DATE('31/12/2024', '%d/%m/%Y'));
  2. Compare strings with strings.

    SELECT * FROM products WHERE code = '123';
  3. Replace zero dates with NULL before tightening the SQL mode or migrating.

    UPDATE events SET happened_at = NULL WHERE happened_at = '0000-00-00';

Related errors

More data values errors

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.

// fewer errors, faster fixes

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.