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)
Published on 01-Apr-2019 15:36:37
