
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- 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
- How to subtract number of days from a date to get the previous date in R?
- 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?
- C# DateTime to add days to the current date
- How to get the records of the last two days from the current date in MySQL?
- MySQL query to get the current date records wherein one of the columns displays current date
- Get current time and date on Android
- Add 11 days to current date in MySQL
- MySQL query to get current datetime and only current date
- Subtract days from current date using Calendar.DATE in Java
- Find the difference between current date and the date records from a MySQL table
- MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
- How to get the current date and time in Java?

Advertisements