Check date difference
mysql>
mysql> CREATE TABLE COMMITTEE_MEMBERS
-> (EmployeeNO INTEGER NOT NULL,
-> BEGIN_DATE DATE NOT NULL,
-> END_DATE DATE ,
-> POSITION CHAR(20) ,
-> PRIMARY KEY (EmployeeNO, BEGIN_DATE));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1990-01-01', '1990-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1991-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1992-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1993-01-01', NULL, 'Chairman');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1990-01-01', '1992-12-31', 'Chairman');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1994-01-01', NULL, 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01', NULL, 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1990-01-01', '1990-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1991-01-01', '1991-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1993-01-01', '1993-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1994-01-01', NULL, 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01', NULL, 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT EmployeeNO, BEGIN_DATE, END_DATE,
-> DATEDIFF(END_DATE, BEGIN_DATE)
-> FROM COMMITTEE_MEMBERS
-> WHERE DATEDIFF(END_DATE, BEGIN_DATE) > 500
-> OR (END_DATE IS NULL AND
-> DATEDIFF(CURRENT_DATE, BEGIN_DATE) > 500)
-> ORDER BY EmployeeNO;
+------------+------------+------------+--------------------------------+
| EmployeeNO | BEGIN_DATE | END_DATE | DATEDIFF(END_DATE, BEGIN_DATE) |
+------------+------------+------------+--------------------------------+
| 2 | 1990-01-01 | 1992-12-31 | 1095 |
| 2 | 1994-01-01 | NULL | NULL |
| 6 | 1991-01-01 | 1992-12-31 | 730 |
| 6 | 1992-01-01 | 1993-12-31 | 730 |
| 6 | 1993-01-01 | NULL | NULL |
| 8 | 1994-01-01 | NULL | NULL |
| 95 | 1994-01-01 | NULL | NULL |
| 112 | 1994-01-01 | NULL | NULL |
+------------+------------+------------+--------------------------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT EmployeeNO, BEGIN_DATE, END_DATE,
-> DATEDIFF(COALESCE(END_DATE, CURRENT_DATE),
-> BEGIN_DATE)
-> FROM COMMITTEE_MEMBERS
-> WHERE DATEDIFF(COALESCE(END_DATE, CURRENT_DATE), BEGIN_DATE) > 500
-> ORDER BY EmployeeNO;
+------------+------------+------------+------------------------------------------------------------------+
| EmployeeNO | BEGIN_DATE | END_DATE | DATEDIFF(COALESCE(END_DATE, CURRENT_DATE),
BEGIN_DATE) |
+------------+------------+------------+------------------------------------------------------------------+
| 2 | 1990-01-01 | 1992-12-31 | 1095 |
| 2 | 1994-01-01 | NULL | 6486 |
| 6 | 1991-01-01 | 1992-12-31 | 730 |
| 6 | 1992-01-01 | 1993-12-31 | 730 |
| 6 | 1993-01-01 | NULL | 6851 |
| 8 | 1994-01-01 | NULL | 6486 |
| 95 | 1994-01-01 | NULL | 6486 |
| 112 | 1994-01-01 | NULL | 6486 |
+------------+------------+------------+------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>
mysql> drop table COMMITTEE_MEMBERS;
Query OK, 0 rows affected (0.00 sec)
Related examples in the same category