Oracle Database Interview Question:

How To Calculate Date and Time Differences in Oracle?

Submitted by: Administrator

If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:

SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3

SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
DAY(9) TO SECOND FROM DUAL;
-- 39901 days and some seconds
39901 7:26:7.0

SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3

SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
DAY(9) TO SECOND
FROM DUAL;
-- 39
Submitted by: Administrator