Common Errors in MySQL

Error: Incorrect DATE value: ''

After upgrading to MySQL 8 we noticed that the MySQL date function fails if given an empty string, and throws the following error message:

Incorrect DATE value: ''

In MySQL we had a condition in the WHERE clause which short circuit evaluated to not try the date function if a value was blank

WHERE Date_Added = '' OR (date(Date_Added) >= '')

But in MySQL 8 the date function appears to be evaluated for date literals even if that side of the OR statement isn't the side that gets short-circuited evaluated.

The solution appears to be to set the session-wide SQL mode to ALLOW_INVALID_DATES

Copyright © 2023 Industrial Hypertext - Software Development Perth, Western Australia | All rights reserved
Contact Us today for an obligation-free meeting to discuss how we can develop an app, website, database, or other kinds of custom software for your business.