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