I started getting this error: Incorrect date value: '2019-02-30' for column... after switch to MySql 8

0 votes
asked May 18, 2020 by rich-c-2789 (15,250 points)
I know this is an invalid date.  A user entered this date on a system that only does basic range checks on the date parts.  We use QIE to track invalid dates entered to trigger an investigation to correct the date.

I have been using a database connection to save this to MySql database.  I previously used MySql 5.7 and these dates were successfully inserted into the database without errors.  After upgrading to MySql 8 I started getting this error:

Incorrect date value: '2019-02-30' for column 'dateCol' at row 1

What changed?

1 Answer

0 votes
The reason this fails is due to additional date validation added to MySql 8.

The new behavior and a possible solution can be found in the bullet list "Be aware of certain properties of date value interpretation in MySQL:" found in this link: here:

"The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31,

     respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a

     warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable

     ALLOW_INVALID_DATES. See Section 5.1.11, “Server SQL Modes”, for more information."

This link also references the change:

It has a nice demo as well:

So, to allow this type of bad dates to be saved without errors or without being transformed by the database you can try adding ALLOW_INVALID_DATES to the sqlMode.  You can do this on the server as in the demo linked above or via the sql_mode JDBC connection url property or by setting the sql_mode in the configuration file on the server.


via connection url:


via configuration file:


Note: There are other changes to date data types.  If you need to preserver invalid dates entered by users you might try storing it as a string rather than a date.  That would isolate your process from other dates validation changes not discussed above.

answered May 18, 2020 by rich-c-2789 (15,250 points)