MySQL - CURRENT_DATE() Function, CURRENT_DATE

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL CURRENT_DATE() function is the synonym for CURDATE(). It used to get the current days date. The resultant value is a string or a numerical value based on the context and, the date returned will be in the 'YYYY-MM-DD' or YYYYMMDD format.

Syntax

Following is the syntax of the above function –

CURRENT_DATE();

Example 1

Following example demonstrates the usage of the CURRENT_DATE() function –

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
|     2021-01-10 |
+----------------+
1 row in set (0.03 sec)

Example 2

Following is an example of this function in numerical context –

mysql> SELECT CURRENT_DATE() +0;
+-------------------+
| CURRENT_DATE() +0 |
+-------------------+
|          20210110 |
+-------------------+
1 row in set (0.00 sec)

Example 3

You can add days to the current date as shown below –

mysql> SELECT CURRENT_DATE()+12;
+-------------------+
| CURRENT_DATE()+12 |
+-------------------+
|          20210122 |
+-------------------+
1 row in set (0.00 sec)

Example 4

We can also subtract the desired number of days from the current date using this function –

mysql> SELECT CURRENT_DATE()-22213;
+----------------------+
| CURRENT_DATE()-22213 |
+----------------------+
|            20,187,897|
+----------------------+
1 row in set (0.00 sec)

Example 5

You can use CURRENT_DATE instead of CURRENT_DATE() to retrieve the current date.

mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
|   2021-01-10 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_DATE+0;
+----------------+
| CURRENT_DATE+0 |
+----------------+
|       20210110 |
+----------------+
1 row in set (0.00 sec)

Example 6

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query calculates the age of the players in days —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, DATEDIFF(CURRENT_DATE(),Date_Of_Birth) as Age_In_Days FROM MyPlayers;
+------------+------------+---------------+-------------+-------------+
| First_Name |  Last_Name | Date_Of_Birth |     Country | Age_In_Days |
+------------+------------+---------------+-------------+-------------+
|    Shikhar |     Dhawan |    1981-12-05 |       India |       14462 |
|   Jonathan |      Trott |    1981-04-22 | SouthAfrica |       14689 |
|     Kumara | Sangakkara |    1977-10-27 |    Srilanka |       15962 |
|      Virat |      Kohli |    1988-11-05 |       India |       11935 |
|      Rohit |     Sharma |    1987-04-30 |       India |       12490 |
|   Ravindra |     Jadeja |    1988-12-06 |       India |       11904 |
|      James |   Anderson |    1982-06-30 |     England |       14255 |
+------------+------------+---------------+-------------+-------------+
7 rows in set (0.00 sec)

Example 7

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following is another example of this function —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price, DATEDIFF(CURRENT_DATE, DispatchDate) as difference_in_days FROM sales;
+-------------+--------------+--------------+-------+--------------------+
| ProductName | CustomerName | DispatchDate | Price | difference_in_days |
+-------------+--------------+--------------+-------+--------------------+
|   Key-Board |         Raja |   2019-09-01 |  7000 |                678 |
|   Earphones |         Roja |   2019-05-01 |  2000 |                801 |
|       Mouse |         Puja |   2019-03-01 |  3000 |                862 |
|      Mobile |       Vanaja |   2019-03-01 |  9000 |                862 |
|     Headset |       Jalaja |   2019-04-06 |  6000 |                826 |
+-------------+--------------+--------------+-------+--------------------+
5 rows in set (0.00 sec)

Example 8

Suppose we have created a table named Subscribers with 5 records in it using the following queries –

mysql> CREATE TABLE Subscribers(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date
);
insert into Subscribers values('Raja', 'Premium', Date('2020-10-21'));
insert into Subscribers values('Roja', 'Basic', Date('2020-11-26'));
insert into Subscribers values('Puja', 'Moderate', Date('2021-03-07'));
insert into Subscribers values('Vanaja', 'Basic', Date('2021-02-21'));
insert into Subscribers values('Jalaja', 'Premium', Date('2021-01-30'));

Following query calculates and displays the remaining number of days for the subscription to complete —

mysql> SELECT SubscriberName, PackageName, SubscriptionDate, DATEDIFF(CURRENT_DATE, SubscriptionDate) as Remaining_Days FROM Subscribers;
+----------------+-------------+------------------+----------------+
| SubscriberName | PackageName | SubscriptionDate | Remaining_Days |
+----------------+-------------+------------------+----------------+
|           Raja |     Premium |       2020-10-21 |            262 |
|           Roja |       Basic |       2020-11-26 |            226 |
|           Puja |    Moderate |       2021-03-07 |            125 |
|         Vanaja |       Basic |       2021-02-21 |            139 |
|         Jalaja |     Premium |       2021-01-30 |            161 |
+----------------+-------------+------------------+----------------+
5 rows in set (0.00 sec)