Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General-CodePudding

I get the error

SQL Error [1525] [HY000]: Incorrect DATE value: '' when running following query:

SELECT * 
FROM cad_grade_horario 
WHERE 1=1 
AND cg_data = '' 
AND cg_agenda_status IN ('0', '1') 
ORDER BY cg_data ASC

The query on my MySQL 5.0 machine works

But server version 8.0.20 doesn't work

What database configuration changes would have caused this?

CodePudding user response：

In MySQL version 5.0, it is possible that the column is defined as a string, or the server's SQL mode includes the option NO_ZERO_IN_DATE, which allows for the use of empty strings or '0000-00-00' as valid date values. However, in MySQL version 8.0, the default SQL mode no longer includes this option, and the empty string value is no longer considered a valid date.

You could enable the option NO_ZERO_IN_DATE in the SQL mode by running the following query:

SET @@SESSION.sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE';

You can also use the following query to check the current SQL mode:

SELECT @@global.sql_mode;

It's important to be aware that changing the SQL mode could affect other queries and scripts in your application, so it's recommended to test the changes thoroughly