# ERROR 1292: Truncated Incorrect Value in MySQL

Source: https://www.bytebase.com/reference/mysql/error/1292-truncated-incorrect-value/

---

## Error Message

```sql
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:

```
ERROR 1292 (22007): Truncated incorrect INTEGER value: '12.5x'
ERROR 1292 (22007): Truncated incorrect DATETIME value: '2026-13-45'
ERROR 1292 (22007): Truncated incorrect time value: '99:99:99'
```

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.

```sql
-- 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:

```sql
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.

```sql
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.

```sql
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`:

```sql
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.

```sql
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:

```sql
-- 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.

```sql
-- 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.

```sql
-- 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:

```sql
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.

```sql
-- 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):

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

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

```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.

```sql
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.

```sql
-- 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`:

```sql
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:

```sql
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

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) checks schema changes against the target MySQL version and `sql_mode`, flagging type-mismatched joins and columns that will trigger 1292 once the change ships. See also [ERROR 1366: Incorrect String Value](/reference/mysql/error/1366-incorrect-string-value) and [ERROR 1406: Data Too Long](/reference/mysql/error/1406-data-too-long-for-column) — both are STRICT mode siblings of 1292.