How MySQL behaves if I use INTERVAL keyword with an invalid date?

MySQLMySQLi Database

Actually, the behavior of MySQL depends upon the allow_invalid_dates mode. If this mode is enabled then MySQL will accept the invalid date and it would perform the date arithmetic as it performs with a valid date. Otherwise, if this mode is inactive then it will not accept the invalid date and would produce NULL as output.

mysql> select '2017-02-30' + INTERVAL 7 day;
+-------------------------------+
| '2017-02-30' + INTERVAL 7 day |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

Now after enabling the allow_invalid_date mode MySQL accepting the invalid dates and performing the date arithmetic as follows −

mysql> set sql_mode = allow_invalid_dates;
Query OK, 0 rows affected (0.05 sec)

mysql> select '2017-02-30' + INTERVAL 7 day;
+-------------------------------+
| '2017-02-30' + INTERVAL 7 day |
+-------------------------------+
| 2017-03-09                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select '2017-11-31' + INTERVAL 7 day;
+-------------------------------+
| '2017-11-31' + INTERVAL 7 day |
+-------------------------------+
| 2017-12-08                    |
+-------------------------------+
1 row in set (0.00 sec)
raja
Published on 19-Feb-2018 07:28:58
Advertisements