- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- How to select a date less than the current date with MySQL?
- Select a column if condition is met in MySQL to fetch records from current date and current date + 1
- MySQL select * and find record with current date
- MySQL query to select date >= current date - 3 weeks?
- MySQL - Insert current date/time?
- MySQL DATE function to return the difference between current date and joining date
- How to compare the first date and the last date from a MySQL column with date records?
- Select dates between current date and 3 months from the current date in MySQL?
- Display record with today and tomorrow’s date from a column with date record in MySQL
- Comparison of varchar date records from the current date in MySQL
- Update MySQL table column by matching date using date() function?
- 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?
- MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
