Returns the number of days of two days after the SQL date minus DATEDIFF ().

The SQL date minus DATEDIFF () returns the number of days between two dates. The definition and usage of the DATEDIFF () function returns the number of days between two dates. The syntax DATEDIFF (date1, date2) date1 and date2 are valid date and time expressions. Note: Only the date part of the value is involved in calculation. DATEDIFF (expr1, expr2): returnsexpr1-expr2asavaluei SQL date offset DATEDIFF () returns two date days

Definition and usage
The DATEDIFF () function returns the number of days between two dates.

Syntax
The DATEDIFF (date1, date2) date1 and date2 parameters are valid date or date/time expressions.

Note: Only the date part of the value is involved in calculation.

DATEDIFF (expr1, expr2): returns expr1-expr2 as a value in days

Instance 1

Select datediff ('2014-12-30 ', '2014-12-29') AS DiffDate

>
Mysql> select datediff ('2017-12-31 23:59:59 ', '2017-12-30 ');
+ ---------------------------------------------- +
| DATEDIFF ('2017-12-31 23:59:59 ', '2017-12-30') |
+ ---------------------------------------------- +
| 1 |
+ ---------------------------------------------- +
1 row in set (0.00 sec)

Mysql>
Mysql> select datediff ('2017-11-30 23:59:59 ', '2017-12-31 ');
+ ---------------------------------------------- +
| DATEDIFF ('2017-11-30 23:59:59 ', '2017-12-31') |
+ ---------------------------------------------- +
|-31 |
+ ---------------------------------------------- +
1 row in set (0.00 sec)

Query the date between two fields

Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (1, 'jason ', 'martin', '000000', '000000', 19960725, 'toronto ', 'programmer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (2, 'alison ', 'mathews', '000000', '000000', 19760321, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (3, 'James ', 'Smith', '000000', '000000', 19781212, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (4, 'cela', 'Rice ', '000000', '000000', 19821024, 'vancouver', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (5, 'Robert ', 'black', '000000', '000000', 19840115, 'vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (6, 'linda ', 'green', '000000', '000000', 19870730, 'New York', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (7, 'David ', 'Larry', '123', '123', 19901231, 'New York ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (8, 'James ', 'cat', '000000', '000000', 19960917, 'vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> * from Employee;
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date | salary | city | description |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| 1 | Jason | Martin | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 4322.78 | New York | Tester |
| 7 | David | Larry | 7897.78 | New York | Manager |
| 8 | James | Cat | 1232.78 | Vancouver | Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> SELECT *
-> FROM employee
-> WHERE (DATEDIFF (curdate (), start_date)/365.25)> 55;
Empty set (0.00 sec)