Recently in a forum someone posted below problem statement.
How to calculate years with months and day between two dates.
Eg. 17-6-2013 – sysdate
1 Year 5 Month 0 Day
Note: Sysdate: = 17 Nov 2014.
So Year, Month and Days are correlated with each other.
Below was my solution for getting the difference between an input date and SYSDATE.
We use below Oracle functions to achieve same.
With Input As
Select To_Date (’06/17/2013′,’MM/DD/YYYY’) d1, trunc (sysdate) d2 from dual
/* Get necessary input*/
Select Trunc ((months_between (d2, d1)) /12) co11, d1, d2 from Input
/* Get Year part using Month_between date function*/
Select Co11 Y, Trunc ((Months_Between (d2, D1) – (Co11*12))) M, D1, d2 From Data
/* Get Month and correlate same with Years by subtracting the calculated year after converting it to month*/
Select Y || ‘ Years ‘ || M || ‘ Months And ‘ || (d2 – to_Date ((EXTRACT (DAY FROM d1)) || ‘/’ || ((EXTRACT (MONTH FROM d1)) + M) || ‘/’ || (EXTRACT (YEAR FROM d1) + Y),’DD/MM/YYYY’)) || ‘ Day’ from data2;
For getting day we use reverse methodology.
Build date using Day part of lower date input, adding calculated correlate month to lower date and same with Year part.
Doing that will ensure if we subtract above form date with a second input (sysdate) we will get remaining Day difference!
1 Years 5 Months And 0 Day
Hope same would help some one else also!