Get the number of days between current date and date field?

To get the number of days between current date and date field, the syntax is as follows −

SELECT DATEDIFF(CURDATE(),STR_TO_DATE(yourColumnName, '%d-%m-%Y')) AS anyAliasName FROM yourTableName;

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

mysql> create table DateDifferenceDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> ArrivalDate varchar(100)
   -> );
Query OK, 0 rows affected (0.55 sec)

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

mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-10-2011');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('01-12-2013');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('31-10-2014');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-04-2016');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('20-08-2018');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('11-03-2019');
Query OK, 1 row affected (0.07 sec)

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

mysql> select *from DateDifferenceDemo;

Here is the output −

+----+-------------+
| Id | ArrivalDate |
+----+-------------+
| 1  | 12-10-2011  |
| 2  | 01-12-2013  |
| 3  | 31-10-2014  |
| 4  | 12-04-2016  |
| 5  | 20-08-2018  |
| 6  | 11-03-2019  |
+----+-------------+
6 rows in set (0.00 sec)

The following is the query to get number of days between current date and date field −

mysql> SELECT DATEDIFF(CURDATE(),STR_TO_DATE(ArrivalDate, '%d-%m-%Y')) AS NumberOfDays from DateDifferenceDemo;

Here is the output −

+--------------+
| NumberOfDays |
+--------------+
| 2708         |
| 1927         |
| 1593         |
| 1064         |
| 204          |
| 1            |
+--------------+
6 rows in set (0.00 sec)

Karthikeya Boyini

Published on 01-Apr-2019 12:06:37

• Related Questions & Answers
• How to get the difference between date records and the current date in MySQL?
• Java Program to get date for all the days of the current week
• MySQL DATE function to return the difference between current date and joining date
• Select dates between current date and 3 months from the current date in MySQL?
• How to subtract number of days from a date to get the previous date in R?
• C# DateTime to add days to the current date
• Get current time and date on Android
• MySQL query to get the current date records wherein one of the columns displays current date
• How to get the records of the last two days from the current date in MySQL?
• Add 11 days to current date in MySQL
• Find the difference between current date and the date records from a MySQL table
• MySQL query to get current datetime and only current date
• Subtract days from current date using Calendar.DATE in Java
• How to get the current date and time in Java?
• MySQL query to fetch date records greater than the current date after adding days with INTERVAL?