MySQL Error 1292

Error number

1292

SQLSTATE

22007

Symbol

ER_TRUNCATED_WRONG_VALUE

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 (an error in strict mode for INSERT, UPDATE and DELETE)

Category

Data Values & Strict Mode

Last reviewed

24 October 2026

What it means

Error 1292 means MySQL could not convert a value to the type it needed, most often a date string that is not a real date or not in YYYY-MM-DD form, and strict mode turned that into an error.

DATE, DATETIME and TIMESTAMP columns accept only real dates in a format MySQL understands, such as '2026-10-24' or '2026-10-24 14:30:00'. A day that does not exist (30 February), a month 13, a UK or US format such as '24/10/2026', an empty string, a zero date (blocked by NO_ZERO_DATE and NO_ZERO_IN_DATE in the default sql_mode) or a TIMESTAMP outside its range of 1970-01-01 to 2038-01-19 UTC is rejected with 1292, naming the value, the column and the row. A time zone offset such as +01:00 is accepted from MySQL 8.0.19, but on 8.0.46 a trailing Z was rejected.

The second wording, Truncated incorrect DOUBLE value, comes from comparing a string column with a number, as in WHERE ref = 100. MySQL then converts every string to a number; a value such as 'A100' cannot be converted cleanly. In a SELECT that is only a warning, but in strict mode an UPDATE or DELETE fails. Without strict mode, a bad date is stored as 0000-00-00 with warning 1265, which is why old tables often hold zero dates. Comparing a DATE column with an invalid date literal in a WHERE clause gives error 1525 instead; badly formed numbers stored in numeric columns give 1366.

Common causes

• A date in a local format ('24/10/2026' or '10/24/2026') is sent instead of '2026-10-24'.
• A date that does not exist, such as '2026-02-30' or '2026-10-00', or an empty string for a missing date instead of NULL.
• An ISO 8601 timestamp from JavaScript or an API, such as '2026-10-24T14:30:00Z', is written to a DATETIME column.
• A TIMESTAMP column receives a date after 2038-01-19 or before 1970, for example an expiry date or a date of birth.
• A VARCHAR column is compared with a number in an UPDATE or DELETE (WHERE ref = 100) and some values are not numeric.
• A table created under an older, non-strict server still holds zero dates, and an ALTER TABLE that copies the table now rejects them.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT id, raw_date
FROM staging_orders
WHERE raw_date IS NOT NULL
  AND STR_TO_DATE(raw_date, '%Y-%m-%d') IS NULL;

SELECT id, ref
FROM orders
WHERE ref NOT REGEXP '^-?[0-9]+$';

SELECT column_name, data_type, column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'orders';

SELECT id, d
FROM old_orders
WHERE YEAR(d) = 0 OR MONTH(d) = 0 OR DAY(d) = 0;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO orders (id, order_date)
VALUES (2, STR_TO_DATE('24/10/2026', '%d/%m/%Y'));

UPDATE orders SET status = 'shipped' WHERE ref = '100';

ALTER TABLE old_orders MODIFY d DATETIME NULL;
UPDATE old_orders SET d = NULL WHERE YEAR(d) = 0 OR MONTH(d) = 0 OR DAY(d) = 0;

Examples

A date that does not exist

CREATE TABLE orders (
    id         INT PRIMARY KEY,
    order_date DATE
);

INSERT INTO orders (id, order_date) VALUES (1, '2026-02-30');  -- there is no 30 February

ERROR 1292 (22007): Incorrect date value: '2026-02-30' for column 'order_date' at row 1

A UK-format date string

CREATE TABLE orders (
    id         INT PRIMARY KEY,
    order_date DATE
);

INSERT INTO orders (id, order_date) VALUES (2, '24/10/2026');

ERROR 1292 (22007): Incorrect date value: '24/10/2026' for column 'order_date' at row 1

An ISO 8601 timestamp ending in Z

CREATE TABLE orders (
    id         INT PRIMARY KEY,
    shipped_at DATETIME
);

INSERT INTO orders (id, shipped_at) VALUES (3, '2026-10-24T14:30:00Z');

ERROR 1292 (22007): Incorrect datetime value: '2026-10-24T14:30:00Z' for column 'shipped_at' at row 1

An UPDATE that compares a VARCHAR column with a number

CREATE TABLE orders (
    id     INT PRIMARY KEY,
    ref    VARCHAR(20),
    status VARCHAR(20)
);
INSERT INTO orders (id, ref) VALUES (1, '100'), (2, 'A100');

UPDATE orders SET status = 'shipped' WHERE ref = 100;  -- 100 is a number, ref is text

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

How to prevent it

Convert dates to YYYY-MM-DD HH:MM:SS in the application or with STR_TO_DATE during imports, send NULL for missing dates, and check staging data before loading it. Choose DATETIME for dates outside 1970 to 2038; TIMESTAMP is for event times near the present.

Compare columns with values of the same type: quote values for VARCHAR columns and do not store numbers as text if you filter on them numerically. Keep strict mode on; without it, bad dates are stored as 0000-00-00 and only a warning tells you.

Common questions

Learn more on CodeWithSQL

• DATE_FORMAT function
• CAST function
• SQL data types explained

Official documentation

• MySQL 8.0 server error message reference
• The DATE, DATETIME and TIMESTAMP types
• Date and time literals
• Type conversion in expression evaluation

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.