Date Functions in Oracle

----- Dates with arthematic operations -------

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE, SYSDATE + 3 FROM DUAL;

SELECT SYSDATE, SYSDATE - 3, (SYSDATE + 72 / 24) FROM DUAL;

SELECT ENAME, HIREDATE, HIREDATE + 3 FROM EMP;

SELECT ENAME, HIREDATE, HIREDATE - 3 FROM EMP;

SELECT ENAME, HIREDATE, SYSDATE - HIREDATE FROM EMP;

SELECT ENAME, (SYSDATE - HIREDATE) / 7 WEEKS
  FROM EMP
 WHERE DEPTNO = 10;

------Add Months Functions-------ADD_MONTHS(D,n)

SELECT SYSDATE, ADD_MONTHS (SYSDATE, 2) FROM DUAL;

SELECT SAL, HIREDATE, ADD_MONTHS (HIREDATE, 12)
  FROM EMP
 WHERE DEPTNO = 20;

------ Months Between Function ---- MONTHS_BETWEEN(D1,D2)

SELECT ENAME,
       HIREDATE,
       SYSDATE,
       MONTHS_BETWEEN (SYSDATE, HIREDATE)
  FROM EMP;

SELECT ENAME,
       HIREDATE,
       SYSDATE,
       MONTHS_BETWEEN (SYSDATE, HIREDATE)
  FROM EMP
 WHERE MONTHS_BETWEEN (SYSDATE, HIREDATE) < 200;

------ Next_Day Function------ NEXT_DAY(D,CHAR)

SELECT SYSDATE, NEXT_DAY (SYSDATE, 'FRI') FROM DUAL;

SELECT SAL, HIREDATE, NEXT_DAY (HIREDATE, 'MONDAY') FROM EMP;

------ Last Day Function------ Last DAY(D)

SELECT SYSDATE, LAST_DAY (SYSDATE) LASTDAY FROM DUAL;

SELECT LAST_DAY (SYSDATE) LAST,
       SYSDATE,
       LAST_DAY (SYSDATE) - SYSDATE Daysleft
  FROM DUAL;

------ ROUND Function ------ROUND(DATE,'FORMAT'

SELECT SYSDATE, ROUND (SYSDATE, 'DAY') FROM DUAL;

SELECT SYSDATE, ROUND (TO_DATE ('16-OCT-11'), 'MONTH') FROM DUAL;

SELECT HIREDATE, ROUND (HIREDATE, 'YEAR') FROM EMP;

------ TRUNC Function ------ TRUNC(DATE,'FORMAT')

SELECT SYSDATE, TRUNC (SYSDATE, 'DAY') FROM DUAL;

SELECT HIREDATE,
       TO_CHAR (HIREDATE, 'DAY'),
       TO_CHAR (HIREDATE, 'D'),
       TRUNC (HIREDATE, 'DAY'),
       TO_CHAR (TRUNC (HIREDATE, 'DAY'), 'DAY'),
       TO_CHAR (TRUNC (HIREDATE, 'DAY'), 'D')
  FROM EMP;

SELECT HIREDATE,
       TO_CHAR (HIREDATE, 'DAY'),
       TO_CHAR (HIREDATE, 'D'),
       ROUND (HIREDATE, 'DAY'),
       TO_CHAR (ROUND (HIREDATE, 'DAY'), 'DAY'),
       TO_CHAR (ROUND (HIREDATE, 'DAY'), 'D')
  FROM EMP;

SELECT HIREDATE, TRUNC (HIREDATE, 'MONTH') FROM EMP;

SELECT HIREDATE, TRUNC (HIREDATE, 'YEAR') FROM EMP;

SELECT SYSDATE, TRUNC (SYSDATE, 'MONTH') FROM DUAL;

SELECT SYSDATE, TRUNC (SYSDATE, 'YEAR') FROM DUAL;