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.

Varun

Published on 19-Feb-2018 06:28:18

• Related Questions & Answers
• What happens if the position of insertion, in MySQL INSERT() function, is out of range?
• What Happens When You Quit Smoking?
• What happens when serum calcium level drops?
• What happens when you control your mind?
• What happens when we type a URL?
• What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
• On passing an out-of-range value in UNIX_TIMESTAMP() or FROM_UNIXTIME() function, what MySQL will return?
• How does MySQL handle out of range numeric values?
• What happens if the output of MySQL TIMEDIFF() function surpass the range value of TIME field?
• What happens when length of object is set to 0 - JavaScript?
• What happens when a negative value is inserted to UNSIGNED column in MySQL?
• Role of CSS: out-of-range Selector
• Get timestamp date range with MySQL Select?
• What happens when JDialog is set with Modality type APPLICATION_MODAL
• What is the range of date time value that we can pass as an argument to MySQL UNIX_TIMESTAMP function?