Get Difference between two date, as Year Month Day Correlated.

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

Expected Output:
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.
1. Month_Between
2. Extract


With Input As
(
Select To_Date (’06/17/2013′,’MM/DD/YYYY’) d1, trunc (sysdate) d2 from dual
/* Get necessary input*/
),
Data as
(
Select Trunc ((months_between (d2, d1)) /12) co11, d1, d2 from Input
/* Get Year part using Month_between date function*/
),
Data2 As
(
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!
*/

Output :
1 Years 5 Months And 0 Day

Hope same would help some one else also!

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s