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)