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?
Couldn’t agree more with Fred — exceptional explanation of why it doesn’t work, but most of us reading this article are probably searching for a way forward when we have NULL in a date field.
Hi Max,
What are you looking for ?
[…] zero values with valid ones, as they may not work correctly in the future. More information: MySQL 8.0 and wrong dates xxxxxxxxxx.xxxxxxxxxxxxx.Register_dt – column has zero default value: 0000-00-00 00:00:00 < … […]
I uploaded data from a csv file onto MySQL Workbench using macOS. The dates are now showing an incorrect format, for example ‘2031-12-20 00:00:00’ when it should really read 2020-12-31 and no time stamp. It seems that because I didn’t format the date in EXCEL I’m now stuck with this problem.
I would appreciate some help. THANK YOU
I’ve no idea how I could help you. You need to re-import or create a script that will parse the lines and update the current records. If the timestamp is not present in the database, there is no way to retrieve it.
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.
The above paragraph doesn’t add up, I can do SELECT STR_TO_DATE( ”, ‘%Y-%m-%d’ ) and it outputs ‘0000-00-00’ which is a valid date in a system that allows ZERO_DATE. So no reason for the error to be thrown using that logic.
Is there any solution available to this error in Mysql 8??
SELECT * FROM tab123 where id = 43871816 and ctime = ‘0’;
produces this error:
[HY000][1525] Incorrect DATETIME value: ‘0’
‘0’ is not a valid datetime, you could use 0 (without the quotes) if you set the SQL_MODE to ” , by default 0 dates or 0 in dates are not valid for datetime.
Now if you really want to use a string you will need to cast it, something like:
select * from tab123 where ctime=cast(‘0’ as datetime);
This will return a warning.