MySQL query to make a date column NULL?

To make a date column null, use ALTER TABLE and MODIFY and set the date to NULL. Following is the syntax −

alter table yourTableName modify column yourColumnName date NULL;

Let us first create a table. Here, we have set the column as NOT NULL −

mysql> create table DemoTable
(
   ShippingDate date NOT NULL
);
Query OK, 0 rows affected (0.78 sec)

Now, insert NULL value in the above table. An error would generate since we have set the column to be NOT NULL −

mysql> insert into DemoTable values(null);
ERROR 1048 (23000) − Column 'ShippingDate' cannot be null

Now, let us alter the table and allow NULL in the above table −

mysql> alter table DemoTable modify column ShippingDate date NULL;
Query OK, 0 rows affected (1.81 sec)
Records : 0 Duplicates : 0 Warnings : 0

Now, again try to insert NULL in the above table using insert command. Error won’t generate since we have altered the table to accept NULL −

mysql> insert into DemoTable values(null);
Query OK, 1 row affected (1.21 sec

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------------+
| ShippingDate |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

Published on 10-Oct-2019 12:04:08