In MySQL 8.0, when using invalid values for date data types, an error is returned. This was not the case in 5.x versions.
Let’s have a look using the table definition of bug 96361:
CREATE TABLE `new_table` (
`id_table` int(11) NOT NULL AUTO_INCREMENT,
`text_table` varchar(45) DEFAULT NULL,
`date_table` date DEFAULT NULL,
PRIMARY KEY (`id_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Now we can try the following statement in MySQL 5.7 and MySQL 8.0:
MySQL 5.7.26> SELECT id_table, text_table
FROM new_table WHERE date_table = '' OR date_table IS NULL;
Empty set, 1 warning (0.01 sec)
MySQL 5.7.26> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'date_table' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)
MySQL 8.0.17> SELECT id_table, text_table
FROM new_table WHERE date_table = '' OR date_table IS NULL;
ERROR 1525 (HY000): Incorrect DATE value: ''
We can see that in MySQL 5.7, a warning is returned but no error.
In earlier version of 5.x it was by default also possible to store DATEs as 0000-00-00. This is not possible anymore neither in 5.7, neither in 8.0 (by default):
mysql> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_table' at row 1To be able to use 0000-00-00 as date the SQL_MODE need to be changed. By default it contains NO_ZERO_IN_DATE,NO_ZERO_DATE.
mysql8> set @@SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql8> select @@SQL_MODE\G
*************************** 1. row ***************************
@@SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
mysql8> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
Query OK, 1 row affected (0.04 sec)
But even when this is changes, invalid dates (other than invalid 0‘s)are of course still considered as errors in MySQL 8.0:
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '';
ERROR 1525 (HY000): Incorrect DATE value: ''
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
| 2 | lefred |
+----------+------------+
1 row in set (0.00 sec)
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-01';
Empty set (0.00 sec)
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-01-32';
ERROR 1525 (HY000): Incorrect DATE value: '2000-01-32'
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '0000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
| 1 | lefred |
| 3 | lefred |
+----------+------------+
2 rows in set (0.00 sec)This is because when comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. Before 8.0 (8.0.16) when the conversion failed, MySQL executed the comparison treating the DATE as a string. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE.
That’s all well and good… except you can’t compare date parameters in stored procedures to null values either.
How can you detect, and correct a null date value passed to the stored procedure, if dteDateParameter is null throws an error?