Skip to content

Instantly share code, notes, and snippets.

@morgo
Created June 17, 2014 18:46
Show Gist options
  • Save morgo/5603720185f0c19de9ed to your computer and use it in GitHub Desktop.
Save morgo/5603720185f0c19de9ed to your computer and use it in GitHub Desktop.
Zero date testcase
Example showing zero date:
mysql [localhost] {msandbox} (test) > CREATE TABLE a (a INT NOT NULL primary key auto_increment, b date);
Query OK, 0 rows affected (0.05 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO a VALUES (NULL, '2014-06-00');
Query OK, 1 row affected (0.01 sec)
# MySQL 5.7 proposal is to merge the SQL mode NO_ZERO_IN_DATE into the definition of STRICT_ALL_TABLES
# Which is on by default.
# See http://www.tocker.ca/2013/11/01/proposal-to-simplify-sql_mode-options.html
mysql [localhost] {msandbox} (test) > set sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO a VALUES (NULL, '2014-06-00');
ERROR 1292 (22007): Incorrect date value: '2014-06-00' for column 'b' at row 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment