1292WARNINGTier 1 — Safe✅ HIGH confidence

Incorrect datetime value

Category: Data TruncationVersions: All MariaDB / MySQL versions

What this means

Error 1292 (SQLSTATE 22007) is raised when a value cannot be interpreted as a valid datetime by the server. This includes invalid date components (February 30th), values outside the supported range, and format strings that don't match the expected pattern. In strict mode this is an error; in non-strict mode a warning with the zero date ('0000-00-00') substituted.

Why it happens

1Inserting a date string in the wrong format (e.g., 'DD/MM/YYYY' instead of 'YYYY-MM-DD')
2Inserting a date with invalid components (month 13, day 32, February 30)
3Inserting a DATETIME value outside the supported range (before 1000-01-01 or after 9999-12-31)
4Inserting an empty string '' into a DATETIME NOT NULL column

How to reproduce

A date string in the wrong format is inserted into a DATE column.

trigger — this will ERROR

SET sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE events (id INT PRIMARY KEY, event_date DATE NOT NULL);
INSERT INTO events VALUES (1, '30/01/2024');  -- wrong format

ERROR 1292 (22007): Incorrect date value: '30/01/2024' for column 'event_date' at row 1

Fix 1: Use ISO 8601 format (YYYY-MM-DD) for date literals

Always use this format for date/time string literals.

fix

INSERT INTO events VALUES (1, '2024-01-30');  -- ISO 8601

Why this works

MariaDB's DATE type expects string literals in 'YYYY-MM-DD' format. ISO 8601 format is unambiguous and accepted consistently across all locales and sql_mode settings.

Fix 2: Use STR_TO_DATE for non-standard date formats

When accepting date strings in formats other than ISO 8601.

fix

INSERT INTO events VALUES (1, STR_TO_DATE('30/01/2024', '%d/%m/%Y'));

Why this works

STR_TO_DATE parses the input string according to the supplied format string and returns a properly typed DATE value, eliminating the format mismatch error.

What not to do

✗

Use the zero date '0000-00-00' as a sentinel value for unknown dates

Why it's wrong: The zero date is not a valid date in many contexts and is rejected by strict mode. Use NULL or a real sentinel date within the valid range instead.

Version notes

MariaDB 10.2+Strict mode enabled by default. The NO_ZERO_DATE and NO_ZERO_IN_DATE sql_mode flags also control acceptance of zero-component dates.

Sources

📚 Official docs: https://mariadb.com/kb/en/date-and-time-literals/

🔧 Source ref: MariaDB Server error code 1292 / ER_TRUNCATED_WRONG_VALUE

📖 Further reading: MariaDB date and time literals

📖 Further reading: STR_TO_DATE function

Confidence assessment

✅ HIGH confidence

Stable. Date literal format requirements are well-documented.