MySQL Date Column Auto Fill with Current Date

You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function.

Case 1:

The syntax is as follows:

yourDateColumnName date default ‘yourDateValue’;

Case 2:

The syntax is as follows:

yourDateColumnName datetime default now();

To understand the above, let us create a table. The query to create a table is as follows:

mysql> create table DefaultCurrentdateDemo
   -> (
   -> LoginDate datetime default now()
   -> );
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into DefaultCurrentdateDemo values();
Query OK, 1 row affected (0.18 sec)

mysql> insert into DefaultCurrentdateDemo values('2017-11-19');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DefaultCurrentdateDemo values('2018-10-21');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DefaultCurrentdateDemo values();
Query OK, 1 row affected (0.23 sec)

mysql> insert into DefaultCurrentdateDemo values('2020-12-24');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from DefaultCurrentdateDemo;

The following is the output:

+---------------------+
| LoginDate |
+---------------------+
| 2019-01-12 20:33:51 |
| 2017-11-19 00:00:00 |
| 2018-10-21 00:00:00 |
| 2019-01-12 20:34:37 |
| 2020-12-24 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

If you want to extract only date, then use the date() method. The query is as follows:

mysql> select date(LoginDate) as OnlyDate from DefaultCurrentdateDemo;

The following is the output:

+------------+
| OnlyDate |
+------------+
| 2019-01-12 |
| 2017-11-19 |
| 2018-10-21 |
| 2019-01-12 |
| 2020-12-24 |
+------------+
5 rows in set (0.00 sec)

Let us set the default value with some date.

The query to create a default value to date column is as follows:

mysql> create table DefaultDate
   -> (
   -> LoginDate date default '2019-01-12'
   -> );
Query OK, 0 rows affected (0.53 sec)

If you do not pass any value to the column then default value will be provided to the column. The query to insert record is as follows:

mysql> insert into DefaultDate values();
Query OK, 1 row affected (0.13 sec)

Display all records from the table using

select statement. The query is as follows:

mysql> select *from DefaultDate;

The following is the output:

+------------+
| LoginDate  |
+------------+
| 2019-01-12 |
+------------+
1 row in set (0.00 sec)