Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How MySQL behaves if I use INTERVAL keyword with an invalid date?
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)
Updated on: 30-Jul-2019
89 Views
Advertisements