MySQL 8.0 and wrong dates

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 1

To 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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

7 Comments

  1. 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?

  2. 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 ?

      mysql> select * from t4;
      +----+------------+
      | id     | date_table     |
      +----+------------+
      |       1 | 2020-06-01 |
      |      2 | 2020-06-01 |
      |      3 | 2020-06-01 |
      |      4 | NULL              |
      +----+------------+
      4 rows in set (0.00 sec)
      
      mysql> select * from t4 where date_table is null;
      +----+------------+
      | id | date_table |
      +----+------------+
      |       4 | NULL              |
      +----+------------+
      1 row in set (0.00 sec)
      
  3. 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.

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.