DATE Arithmetic

By using arithmetic operators you can perform calculations on dates.

For example

SELECT EMPNO,ENAME,SAL,HIREDATE,HIREDATE+100 “NEW DATE”

FROM EMP;

 EMPNO ENAME             SAL HIREDATE  NEW DATE

---------- ---------- ---------- --------- ---------

 7369 SMITH             800 17-DEC-80 27-MAR-81

 7499 ALLEN            1600 20-FEB-81 31-MAY-81

 7521 WARD             1250 22-FEB-81 02-JUN-81

  7566 JONES            2975 02-APR-81 11-JUL-81

  7654 MARTIN           1250 28-SEP-81 06-JAN-82

  7698 BLAKE            2850 01-MAY-81 09-AUG-81

  7782 CLARK            2450 09-JUN-81 17-SEP-81

  7788 SCOTT            3000 19-APR-87 28-JUL-87

  7839 KING             5000 17-NOV-81 25-FEB-82

  7844 TURNER           1500 08-SEP-81 17-DEC-81

  7876 ADAMS            1100 23-MAY-87 31-AUG-87

     EMPNO ENAME             SAL HIREDATE  NEW DATE

---------- ---------- ---------- --------- ---------

      7900 JAMES             950 03-DEC-81 13-MAR-82

      7902 FORD             3000 03-DEC-81 13-MAR-82

      7934 MILLER           1300 23-JAN-82 03-MAY-82

Example2: Assume that you want numbers of weeks for each employee are worked for the company

SELECT EMPNO,ENAME,HIREDATE,(SYSDATE-HIREDATE)/7 WEEKS_AMMOUNT

FROM EMP;

  EMPNO ENAME      HIREDATE  WEEKS_AMMOUNT

------- ---------- --------- -------------

   7369 SMITH      17-DEC-80    1606.80078

   7499 ALLEN      20-FEB-81    1597.51507

   7521 WARD       22-FEB-81    1597.22935

   7566 JONES      02-APR-81    1591.65792

   7654 MARTIN     28-SEP-81    1566.08649

   7698 BLAKE      01-MAY-81    1587.51507

   7782 CLARK      09-JUN-81    1581.94364

   7788 SCOTT      19-APR-87    1276.22935

   7839 KING       17-NOV-81    1558.94364

   7844 TURNER     08-SEP-81    1568.94364

   7876 ADAMS      23-MAY-87    1271.37221

  EMPNO ENAME      HIREDATE  WEEKS_AMMOUNT

------- ---------- --------- -------------

   7900 JAMES      03-DEC-81    1556.65792

   7902 FORD       03-DEC-81    1556.65792

   7934 MILLER     23-JAN-82    1549.37221