SQL Date Subtraction DateDiff () returning two date days

SQL Date Subtraction DateDiff () returning two date days

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

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

Note: Only the date part of the value participates in the calculation.

DATEDIFF (EXPR1,EXPR2): Returns EXPR1-EXPR2 as a value in the days

Instance One

SELECT DATEDIFF (' 2008-12-30 ', ' 2008-12-29 ') as Diffdate

MySQL Tutorial
mysql> SELECT DATEDIFF (' 1997-12-31 23:59:59 ', ' 1997-12-30 ');
+------------------------ ----------------------+
| DATEDIFF (' 1997-12-31 23:59:59 ', ' 1997-12-30 ') |
+----------------------------------------------+
|                                              1 |
+----------------------------------------------+
1 row in Set (0.00 sec)

Mysql>
mysql> SELECT DATEDIFF (' 1997-11-30 23:59:59 ', ' 1997-12-31 ');
+----------------------------------------------+
| DATEDIFF (' 1997-11-30 23:59:59 ', ' 1997-12-31 ') |
+----------------------------------------------+
| -31 |
+----------------------------------------------+
1 row in Set (0.00 sec)

Find the date between two fields

Mysql> CREATE TABLE Employee (
   ->     id             int,
   ->     first_name     VARCHAR,
   ->     last_name      VARCHAR,
   ->     start_date    date,
   ->     end_date      date,
    ->     salary        FLOAT (8,2),
    ->     city          VARCHAR (Ten),
   ->     description   VARCHAR (a)
   ->);
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 ', ' 19960725 ', ' 20060725 ', 1234.56, ' 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 ', ' 19760321 ', ' 19860221 ', 6661.78, ' 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 ', ' 19781212 ', ' 19900315 ', 6544.78, ' 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, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' 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 ', ' 19840115 ', ' 19980808 ', 2334.78, ' 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 ', ' 19870730 ', ' 19960104 ', 4322.78, ' 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 ', ' 19901231 ', ' 19980212 ', 7897.78, ' 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 ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 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)