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)

Chandu yadav

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• MySQL - Insert current date/time?
• How to select a date less than the current date with MySQL?
• MySQL select * and find record with current date
• MySQL DATE function to return the difference between current date and joining date
• MySQL query to select date >= current date - 3 weeks?
• Select a column if condition is met in MySQL to fetch records from current date and current date + 1
• Select dates between current date and 3 months from the current date in MySQL?
• Comparison of varchar date records from the current date in MySQL
• Insert current date in datetime format MySQL?
• Filter query by current date in MySQL
• Fetch date records comparing with the current date’s day and month in MySQL
• How to compare the first date and the last date from a MySQL column with date records?
• Grab where current date and the day before with MySQL?
• Set a MySQL field with the current date (UNIX_TIMESTAMP(now))
• Add some months to current date using Java with MySQL?