Wednesday, October 23, 2013

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect