Monday, April 4, 2016

Difference between 2 dates or Number of years, months, days, hours between 2 dates in oracle

SELECT ename, EXTRACT(YEAR FROM (SYSDATE - hiredate) YEAR TO MONTH )
   || ' years '
   || EXTRACT(MONTH FROM (SYSDATE - hiredate) YEAR TO MONTH )
   || ' months'  "Interval"
FROM emp ;

ENAME Interval
SMITH 35 years 4 months
ALLEN 35 years 1 months
WARD 35 years 1 months
JONES 35 years 0 months
MARTIN 34 years 6 months
BLAKE 34 years 11 months
CLARK 34 years 10 months
SCOTT 29 years 0 months
KING 34 years 5 months
TURNER 34 years 7 months
ADAMS 28 years 10 months
JAMES 34 years 4 months
FORD 34 years 4 months
MILLER 34 years 2 months



SELECT ename,
   EXTRACT(DAY FROM (SYSDATE - hiredate) DAY TO SECOND )
   || ' days '
   || EXTRACT(HOUR FROM (SYSDATE - hiredate) DAY TO SECOND )
   || ' hours' "Interval"
FROM emp;


ENAME Interval
SMITH 12892 days 14 hours
ALLEN 12827 days 14 hours
WARD 12825 days 14 hours
JONES 12786 days 14 hours
MARTIN 12607 days 14 hours
BLAKE 12757 days 14 hours
CLARK 12718 days 14 hours
SCOTT 10578 days 14 hours
KING 12557 days 14 hours
TURNER 12627 days 14 hours
ADAMS 10544 days 14 hours
JAMES 12541 days 14 hours
FORD 12541 days 14 hours
MILLER 12490 days 14 hours

No comments:

Post a Comment