How is it possible to store date such as February 30 in a MySQL date column?

Suppose we want to store the date such as February 30 in a MySQL table then we must have to first set ALLOW_INVALID_DATES mode enabled.

For example, I am trying to add, without enabling ALLOW_INVALID_DATES mode, such kind of date in a table then MySQL will give an error as follows −

mysql> Insert into date_testing(date) values('2017-02-30');
ERROR 1292 (22007): Incorrect date value: '2017-02-30' for column
'Date' at row1

Now we need to enable ALLOW_INVALID_DATES mode enabled as follows −

mysql> SET sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> Insert into date_testing(date) values('2017-02-30');
Query OK, 1 row affected (0.14 sec)

mysql> select * from date_testing;
+------------+
| Date |
+------------+
| 2017-02-30 |
+------------+
1 row in set (0.00 sec)

Above MySQL query will allow us to insert such kind of invalid dates in a column.

Ankitha Reddy

Published on 19-Feb-2018 13:04:00