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 08:34:00

• Related Questions & Answers
• Is it okay to store double and date in VARCHAR with MySQL?
• Add 30 days to date in a MySQL table with arrival date records
• MySQL query to delete a DATE older than 30 days from another date?
• MySQL Order By date column and integer column, but specify ordering rules of integer column? Is it possible?
• How MySQL evaluates if I store date along with time value in a column having DATE data type?
• How can I store ‘0000-00-00’ as a date in MySQL?
• MySQL CAST as DATE?
• How to cast DATETIME as a DATE in MySQL?
• How to update a MySQL date type column?
• How to set default date time as system date time in MySQL?
• How to add 30 minutes to a JavaScript Date object?
• What is the easiest way to store date in MySQL database?
• MySQL date column auto fill with current date?
• How to compare the first date and the last date from a MySQL column with date records?
• How to convert a MySQL date to JavaScript date?