MySQL rejects a date with 'Incorrect date value' on INSERT
Incorrect 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
Values like 31-12-2024 or 12/31/2024 fail, while 2024-12-31 works.The string is day-first or month-firstFix - 2
Only some dates fail, such as 2024-04-31 or 2023-02-29.The day does not exist in that monthFix - 3
The 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
- 1
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL error 1292 on a statement that compares text with a number
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'
MySQL compares a string with a number by turning both into floating-point numbers.
2 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix