Take a look here
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:96012348060 You might need to take care around leap years etc, but this should get you started
SQL> with emp_dates as
2 ( select to_date('21/04/2011 21:12:12','dd/mm/yyyy hh24:mi:ss') date_from,
3 to_date('17/09/2015 23:45:19','dd/mm/yyyy hh24:mi:ss') date_to
4 from dual
5 )
6 select
7 trunc(months_between(date_to,date_from)) total_mths,
8 date_to - date_from total_days,
9 (date_to - date_from)*24 total_hours,
10 (date_to - date_from)*24*60 total_mins,
11 (date_to - date_from)*24*60*60 total_secs
12 from emp_dates;
TOTAL_MTHS TOTAL_DAYS TOTAL_HOURS TOTAL_MINS TOTAL_SECS
---------- ---------- ----------- ---------- ----------
52 1610.10633 38642.5519 2318553.12 139113187
SQL>
SQL>
SQL>
SQL> with emp_dates as
2 ( select to_date('21/04/2011 21:12:12','dd/mm/yyyy hh24:mi:ss') date_from,
3 to_date('17/09/2015 23:45:19','dd/mm/yyyy hh24:mi:ss') date_to
4 from dual
5 )
6 select
7 trunc(months_between(date_to,date_from)/12) years,
8 mod(trunc(months_between(date_to,date_from)),12) mths,
9 trunc(date_to - add_months(date_from,trunc(months_between(date_to,date_from)))) days,
10 trunc(24*mod(date_to - date_from,1)) hrs,
11 trunc(60*mod(24*mod(date_to - date_from,1),1)) mins,
12 mod((date_to - date_from)*24*60*60,60) secs
13 from emp_dates;
YEARS MTHS DAYS HRS MINS SECS
---------- ---------- ---------- ---------- ---------- ----------
4 4 27 2 33 7