ERROR 1292: Truncated Incorrect Value in MySQL

Error Message#

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

You may also see the same error code with different type names depending on what MySQL was trying to convert:

In a non-strict sql_mode, the same condition becomes a warning instead of an error and the value is silently truncated — which is its own debugging nightmare.

What Triggers This Error#

1292 fires when MySQL has to convert a value to a target type and the source value is not fully valid for that type. With STRICT_TRANS_TABLES (default since MySQL 5.7), the conversion failure is an error; without it, MySQL truncates the value and emits a warning. The most common triggers:

• Comparing a string column to a number — WHERE varchar_col = 5 triggers an implicit cast of every row
• Inserting a malformed date or datetime — '2026-02-30', '2026-13-01', '2026/04/27 25:00:00'
• Inserting a string that doesn't fully parse as a number — '12.5x', '1,234.50' (comma thousands separator)
• JOIN on columns of different types — joining VARCHAR id to INT id on every row triggers per-row casts
• Implicit collation conversion in WHERE — comparing utf8mb4_general_ci column to latin1_swedish_ci literal
• Boolean stored as string — inserting 'true' / 'false' into a TINYINT(1) column
• Empty string into numeric column — INSERT INTO t (price) VALUES ('') with strict mode

Fix by Scenario#

String column compared to a number#

The classic case. A VARCHAR column compared to an integer literal forces MySQL to cast every row's string to a number. If any row has non-numeric data, you get 1292.

-- fails when any row in customer_id is non-numeric
SELECT * FROM orders WHERE customer_id = 5;
-- ERROR 1292: Truncated incorrect DOUBLE value: 'abc'

Fix: quote the literal so it stays a string comparison:

SELECT * FROM orders WHERE customer_id = '5';

Better: fix the data type. If customer_id is always numeric, change the column to INT. If it can be alphanumeric, keep it as VARCHAR and always quote in queries.

ALTER TABLE orders MODIFY customer_id INT;

This also fixes the index efficiency problem — implicit casts disable index usage on the column being cast.

Malformed date or datetime#

MySQL's date parser is strict in STRICT_TRANS_TABLES. Any out-of-range component (month > 12, day > 31, hour > 23) triggers 1292.

INSERT INTO events (event_at) VALUES ('2026-02-30 12:00:00');
-- ERROR 1292: Incorrect datetime value: '2026-02-30 12:00:00'

INSERT INTO events (event_at) VALUES ('2026/04/27 25:00:00');
-- ERROR 1292: Incorrect datetime value: '2026/04/27 25:00:00'

Fix: validate dates at the application layer before insert. The most common source is parsing user input or third-party feeds without checking each field. If the source data contains invalid dates and you can't fix it upstream, use STR_TO_DATE and a CASE:

INSERT INTO events (event_at)
SELECT
    CASE
        WHEN STR_TO_DATE(raw_date, '%Y-%m-%d %H:%i:%s') IS NOT NULL
        THEN STR_TO_DATE(raw_date, '%Y-%m-%d %H:%i:%s')
        ELSE NULL
    END
FROM staging_events;

Note: STR_TO_DATE returns NULL (not an error) for invalid input even in strict mode, so it's safer for ETL.

String that doesn't fully parse as a number#

MySQL converts strings to numbers by reading characters until it hits a non-numeric one. '12abc' becomes 12 with a warning; with strict mode, the warning becomes a 1292 error.

INSERT INTO products (price) VALUES ('12.5x');
-- ERROR 1292: Truncated incorrect DECIMAL value: '12.5x'

INSERT INTO products (price) VALUES ('1,234.50');
-- ERROR 1292: Truncated incorrect DECIMAL value: '1,234.50'

Fix: clean the input before insert. Common preprocessing:

-- strip thousands separators
INSERT INTO products (price) VALUES (REPLACE('1,234.50', ',', ''));

-- strip non-numeric trailing characters
INSERT INTO products (price) VALUES (
    CAST(REGEXP_REPLACE('12.5x', '[^0-9.]', '') AS DECIMAL(10,2))
);

Better: validate at the application layer. SQL-side cleaning is a fallback for ETL from dirty sources, not a primary input path.

JOIN on columns of different types#

A join condition between VARCHAR and INT columns casts every row on both sides. Any non-numeric value in the VARCHAR side triggers 1292.

-- orders.customer_id is VARCHAR, customers.id is INT
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- ERROR 1292: Truncated incorrect DOUBLE value: 'GUEST'

Fix: align the types. The cheaper direction (in storage and join cost) is usually VARCHAR → INT if all values are numeric, or INT → VARCHAR if not.

-- if all customer_id values are numeric
ALTER TABLE orders MODIFY customer_id INT;

-- if not, change customers.id to VARCHAR (more invasive)
ALTER TABLE customers MODIFY id VARCHAR(20);

Until the schema change ships, exclude non-numeric rows in the join:

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = CAST(c.id AS CHAR)
WHERE o.customer_id REGEXP '^[0-9]+$';

This bypasses 1292 but disables the join index — only acceptable as a temporary workaround.

Boolean as string in TINYINT(1)#

Application code that sends 'true' / 'false' strings to a TINYINT(1) column triggers 1292 because 'true' doesn't parse as a number.

-- fails
INSERT INTO users (is_active) VALUES ('true');
-- ERROR 1292: Truncated incorrect INTEGER value: 'true'

Fix: send 1 / 0 from the application, or TRUE / FALSE (which MySQL interprets as 1/0):

INSERT INTO users (is_active) VALUES (1);
INSERT INTO users (is_active) VALUES (TRUE);

If you can't change the application, convert in SQL:

INSERT INTO users (is_active) VALUES (CASE WHEN 'true' = 'true' THEN 1 ELSE 0 END);

Empty string into numeric column#

In strict mode, an empty string '' is not zero — it's an invalid number.

INSERT INTO products (price) VALUES ('');
-- ERROR 1292: Truncated incorrect DECIMAL value: ''

Fix: the application should send NULL or 0 explicitly. Bulk-loading from CSV is a frequent source — many CSV exports use empty cells for missing numeric values.

-- LOAD DATA approach: convert empty to NULL
LOAD DATA INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
(name, @price)
SET price = NULLIF(@price, '');

Checking the warnings (non-strict mode)#

If your sql_mode is non-strict, 1292 may not surface as an error — but the value is still being truncated. Find these silent corruptions with SHOW WARNINGS:

INSERT INTO products (price) VALUES ('12.5x');
-- Query OK, 1 row affected, 1 warning (0.00 sec)

SHOW WARNINGS;
-- +---------+------+----------------------------------------------+
-- | Level   | Code | Message                                      |
-- +---------+------+----------------------------------------------+
-- | Warning | 1292 | Truncated incorrect DECIMAL value: '12.5x'   |
-- +---------+------+----------------------------------------------+

For ongoing data quality, switch to strict mode in production:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

The default since MySQL 5.7 already includes STRICT_TRANS_TABLES — but legacy environments often turn it off to avoid breaking old code, then accumulate years of silent truncation.

Prevention#

• Quote string literals when comparing to string columns — never let MySQL guess the type
• Align column types across joined tables; don't rely on implicit conversion to bridge mismatches
• Validate dates and numbers at the application layer before they reach SQL
• For ETL from external sources, use STR_TO_DATE and NULLIF(..., '') to convert invalid values to NULL rather than letting them error or get silently truncated
• Keep STRICT_TRANS_TABLES enabled in production — silent truncation is harder to find than 1292 errors
• After every batch insert, run SHOW WARNINGS to catch type-conversion issues that didn't escalate to errors