DATE FUNCTION IN SQL
1- MONTHS_BETWEEN Function : finds the number of months between two dates
For example assume that you want find number of months between an employee hire date
And sysdate
Type
Select empno,ename,sal,hiredate,MONTHS_BETWEEN(’03-OCT-11’,HIREDATE)
FROM EMP;
EMPNO ENAME SAL HIREDATE MONTHS_BETWEEN('03-OCT-11',HIREDATE)
--------- ---------- ---------- --------- ------------------------------------
7369 SMITH 800 17-DEC-80 369.548387
7499 ALLEN 1600 20-FEB-81 367.451613
7521 WARD 1250 22-FEB-81 367.387097
7566 JONES 2975 02-APR-81 366.032258
7654 MARTIN 1250 28-SEP-81 360.193548
7698 BLAKE 2850 01-MAY-81 365.064516
7782 CLARK 2450 09-JUN-81 363.806452
7788 SCOTT 3000 19-APR-87 293.483871
7839 KING 5000 17-NOV-81 358.548387
7844 TURNER 1500 08-SEP-81 360.83871
7876 ADAMS 1100 23-MAY-87 292.354839
EMPNO ENAME SAL HIREDATE MONTHS_BETWEEN('03-OCT-11',HIREDATE)
--------- ---------- ---------- --------- ------------------------------------
7900 JAMES 950 03-DEC-81 358
7902 FORD 3000 03-DEC-81 358
7934 MILLER 1300 23-JAN-82 356.354839
1- A DD_MONTHS function :by using these function you can adds a number of calendar month to a specified date.
For example
Select empno,ename,sal,hiredate,ADD_MONTHS (HIREDATE,7)
FROM EMP;
EMPNO ENAME SAL HIREDATE ADD_MONTH
------ ---------- ---------- --------- ---------
7369 SMITH 800 17-DEC-80 17-JUL-81
7499 ALLEN 1600 20-FEB-81 20-SEP-81
7521 WARD 1250 22-FEB-81 22-SEP-81
7566 JONES 2975 02-APR-81 02-NOV-81
7654 MARTIN 1250 28-SEP-81 28-APR-82
7698 BLAKE 2850 01-MAY-81 01-DEC-81
7782 CLARK 2450 09-JUN-81 09-JAN-82
7788 SCOTT 3000 19-APR-87 19-NOV-87
7839 KING 5000 17-NOV-81 17-JUN-82
7844 TURNER 1500 08-SEP-81 08-APR-82
7876 ADAMS 1100 23-MAY-87 23-DEC-87
EMPNO ENAME SAL HIREDATE ADD_MONTH
------ ---------- ---------- --------- ---------
7900 JAMES 950 03-DEC-81 03-JUL-82
7902 FORD 3000 03-DEC-81 03-JUL-82
7934 MILLER 1300 23-JAN-82 23-AUG-82
2-NEXT DAY function : by using these function you can return the date of next day.
For example
Select empno, ename, NEXT_DAY (’03-OCT-2011’,’Friday’) Friday
From emp;
select next_day('03-oct-2011','friday')
from dual
NEXT_DAY(
---------
07-OCT-11
3- LAST_DAY function : you can finds the date of the last day of month
For example
select last_day('01-oct-2011')
from dual;
LAST_DAY(
---------
31-OCT-11
3- ROUND And TRUNC functionROUND returns a date rounded to the unit specified, TURANC returns a date truncated to the unit specified.
For example
SELECT ENAME,HIREDATE,
ROUND (HIREDATE,’MONTH’),
TRUNC(HIREDATE,’MONTH’)
FROM EMP;
ENAME HIREDATE ROUND(HIR TRUNC(HIR
---------- --------- --------- ---------
SMITH 17-DEC-80 01-JAN-81 01-DEC-80
ALLEN 20-FEB-81 01-MAR-81 01-FEB-81
WARD 22-FEB-81 01-MAR-81 01-FEB-81
JONES 02-APR-81 01-APR-81 01-APR-81
MARTIN 28-SEP-81 01-OCT-81 01-SEP-81
BLAKE 01-MAY-81 01-MAY-81 01-MAY-81
CLARK 09-JUN-81 01-JUN-81 01-JUN-81
SCOTT 19-APR-87 01-MAY-87 01-APR-87
KING 17-NOV-81 01-DEC-81 01-NOV-81
TURNER 08-SEP-81 01-SEP-81 01-SEP-81
ADAMS 23-MAY-87 01-JUN-87 01-MAY-87
ENAME HIREDATE ROUND(HIR TRUNC(HIR
---------- --------- --------- ---------
JAMES 03-DEC-81 01-DEC-81 01-DEC-81
FORD 03-DEC-81 01-DEC-81 01-DEC-81
MILLER 23-JAN-82 01-FEB-82 01-JAN-82
Posts
