EC
1292
MariaDBWARNINGCommonData TruncationHIGH confidence

Incorrect datetime value

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
  1. 1Inserting a date string in the wrong format (e.g., 'DD/MM/YYYY' instead of 'YYYY-MM-DD')
  2. 2Inserting a date with invalid components (month 13, day 32, February 30)
  3. 3Inserting a DATETIME value outside the supported range (before 1000-01-01 or after 9999-12-31)
  4. 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
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

expected output

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

WHEN Always use this format for date/time string literals.

Use ISO 8601 format (YYYY-MM-DD) for date literals
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 When accepting date strings in formats other than ISO 8601.

Use STR_TO_DATE for non-standard date formats
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

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 documentation ↗

MariaDB Server error code 1292 / ER_TRUNCATED_WRONG_VALUE

MariaDB date and time literals ↗STR_TO_DATE function ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors