Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL rejects a date with 'Incorrect date value' on INSERT

ErrorIncorrect date value: '31-12-2024'

Short answer

MySQL expects dates as year-month-day. In strict mode (the default in 8.0) a value in another order, a day that does not exist, or a zero date is an error instead of a silent zero. Convert the string with STR_TO_DATE, correct the value, or send NULL, and leave strict mode on.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL mode, then test the exact value with a plain SELECT. A valid value returns a date; a bad one shows NULL and a warning.

    SELECT @@SESSION.sql_mode;
    SELECT STR_TO_DATE('31-12-2024', '%d-%m-%Y');
    SHOW WARNINGS;
  2. 2Match what you see, then jump to its fix

    1. 1Values like 31-12-2024 or 12/31/2024 fail, while 2024-12-31 works.The string is day-first or month-firstFix
    2. 2Only some dates fail, such as 2024-04-31 or 2023-02-29.The day does not exist in that monthFix
    3. 3The failing value is 0000-00-00, or a date with a zero month or day (2024-00-10).A zero date such as 0000-00-00 comes from an old importFix

What does the error look like?

  • Incorrect date value: '31-12-2024'
  • Incorrect %s value: '%s'

Cause 1 of 3

The string is day-first or month-first

1Check

Date parts must be given year-month-day. The manual says to use STR_TO_DATE to convert other orders.

SELECT STR_TO_DATE('31-12-2024', '%d-%m-%Y') AS parsed;

2Fix

Convert in the statement, or format the date in application code before binding it.

INSERT INTO orders (placed_on) VALUES (STR_TO_DATE('31-12-2024', '%d-%m-%Y'));

3Verify

Read the row back.

SELECT placed_on FROM orders ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The day does not exist in that month

1Check

Without ALLOW_INVALID_DATES the server requires a legal date, not only month 1-12 and day 1-31. With strict mode it raises an error; without strict mode it stores 0000-00-00 with a warning.

SELECT @@SESSION.sql_mode LIKE '%STRICT%' AS strict_on;

2Fix

Fix the value in the source (a form, a CSV, a loop that adds 31 days). Do not turn strict mode off to hide it, because bad dates would turn into zero dates.

INSERT INTO orders (placed_on) VALUES ('2024-04-30');

3Verify

Insert the corrected value.

SELECT DATE('2024-04-30');

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A zero date such as 0000-00-00 comes from an old import

1Check

The 8.0 default modes include NO_ZERO_DATE and NO_ZERO_IN_DATE. With strict mode, such values are an error unless IGNORE is given.

SELECT @@SESSION.sql_mode;

2Fix

Send NULL for 'no date' (the column must allow NULL), or clean the data before the load.

ALTER TABLE orders MODIFY shipped_on DATE NULL;
UPDATE staging SET shipped_on = NULL WHERE shipped_on = '0000-00-00';

3Verify

No zero dates are left.

SELECT COUNT(*) FROM staging WHERE shipped_on IS NULL;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Send dates to the database as YYYY-MM-DD strings or as date objects of the driver.
  • Use NULL for missing dates, not a zero date.
  • Keep strict mode on, so bad values fail where they are written.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.