Hi all,
i have this view
with t(id, dt) as (select 1, to_date('02/06/2010','MM/DD/YYYY') from dual union all
select 2, to_date('11/29/2001','MM/DD/YYYY') from dual union all
select 3, to_date('02/06/2011','MM/DD/YYYY') from dual union all
select 4, to_date('10/10/2011','MM/DD/YYYY') from dual
)
--
-- end of test data
--
select id, dt
,floor(months_between(sysdate,dt)/12) as yrs
,floor(mod(months_between(sysdate,dt),12)) as months
-- The day will be ambiguous because of the different number of days in a month
-- you could devise your own algorithm to give your desired result, but this is a good approximation
,floor(sysdate-(add_months(dt,floor(months_between(sysdate,dt))))) as dys
from t
/
ID DT YRS MONTHS DYS
---------- ----------- ---------- ---------- ----------
1 06-FEB-2010 5 8 22
2 29-NOV-2001 13 10 29
3 06-FEB-2011 4 8 22
4 10-OCT-2011 4 0 18
result is 26 years, 26 months, 91 days.
And need substract YRS, MONTHS and DYS as format
clear years, months from 1 to 12 and days from 1 to 30 as
91 days = 30 x 3 + 1 => 1 day plus 3 months
26 months + 3 months = 29 months => 24 months + 5 months = 2 years + 5 months
and 26 years + 2 years=28 years
desire result : 28 years, 5 months and 1 day.
Is there any function which give me this result from above query?
regards,
Gordan