What happens when MySQL encounters an out-of-range date?
The response of MySQL on encountering out-of-range or invalid date will depend upon SQL MODE. If we have enabled ALLOW_INVALID_DATES mode then MySQL will convert the out of range values into all zeros (i.e. ‘0000:00:00 00:00:00’) and also stores the same in the table without producing any error or warning.
For example, we can change SQL MODE as follows and then insert the out-of-range −
mysql> set sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)
mysql> Insert into order1234(productname, quantity, orderdate) values('A', 500, '999-05-100');
Query OK, 1 row affected, 1 warning (0.13 sec)
mysql> Select * from order1234;
+-------------+----------+---------------+
| ProductName | Quantity | OrderDate |
+-------------+----------+---------------+
| A | 500 | 0000-00-00 |
+-------------+----------+---------------+
1 row in set (0.00 sec)We can see MySQL converts the out-of-range value in all zeros.
Published on 19-Feb-2018 06:28:18
