Hello experts,
DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I am trying to ge the difference between two dates in years:months:days:hours:minutes:seconds. I am able to do it for all except for days. Please help me on this.
with inputs(id, s_dt, e_dt) as
(
select 1, date'2014-07-28', sysdate from dual union all
select 2, date'2014-07-30', sysdate from dual union all
select 3, to_date('20140728231324','yyyymmddhh24miss'), date'2015-12-30' from dual
),
calculate as
(
select t.*,
floor(m_bet/12) as years,
floor(m_bet) - (floor(m_bet/12) * 12) as months,
floor(e_dt - trunc(e_dt,'mm')) + 1 as days, --Incorrect
extract(hour from d_int) as hours,
extract(minute from d_int) as minutes,
extract(second from d_int) as seconds
from
(
select t.*, months_between(e_dt, s_dt) as m_bet, numtodsinterval(e_dt - s_dt,'day') as d_int from inputs t
) t
)
select id, s_dt, e_dt, years||':'||months||':'||days||':'||hours||':'||minutes||':'||seconds as time_diff
from calculate;
Regards,
Ranagal