How To Calculate Date and Time Differences? - Oracle DBA FAQ - Understanding SQL Basics

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;
  -- 39901 days and some fractional seconds
39901 7:26:7.723000000