mysql> show create table date_tests;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| date_tests | CREATE TABLE `date_tests` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`d_test` date NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
+----+------------+
2 rows in set (0.01 sec)
mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date ;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.01 sec)
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
+----+------------+
5 rows in set (0.00 sec)
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into date_tests(d_test) value('');
ERROR 1292 (22007): Incorrect date value: '' for column 'd_test' at row 1
mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
| 6 | 0000-00-00 |
| 7 | NULL |
+----+------------+
7 rows in set (0.00 sec)
mysql> select id, d_test from date_tests where d_test is null;
+----+--------+
| id | d_test |
+----+--------+
| 5 | NULL |
| 7 | NULL |
+----+--------+
2 rows in set (0.00 sec)
mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)
mysql> select id, d_test from date_tests where d_test is null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
4 rows in set (0.00 sec)
mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date DEFAULT '1970-1-1';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | NULL |
+----+------------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL DEFAULT '1970-1-1';
Query OK, 7 rows affected, 1 warning (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 1
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
+----+------------+
7 rows in set (0.01 sec)
mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
+----+------------+
8 rows in set (0.00 sec)
mysql> insert into date_tests(id) value(null);
Query OK, 1 row affected (0.01 sec)
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
| 11 | 1970-01-01 |
+----+------------+
9 rows in set (0.01 sec)