asked on

Obtaining date difference (interval?) in Years, Months, Days instead of just days or months (plus fractions)

Hello,

I need to present date differences (interval?) in years, months, and days instead of just days. I'm looking to do this in simple SQL (instead of Java, PL/SQL, etc.) on the other hand I'm open to all suggestions.

The two dates may both change (e.g. two different dates from the same row or different rows/tables), one may change while the other stays the same, or well, they might both from two places and don't change. I looked for Oracle functions but the best I can find is months_between. This still doesn't help as I need it to be people friendly (e.g. years, months, and days).

The dates are all truncated (e.g. trunc(sysdate)) so the hours, minutes, seconds, hundredths of a second, etc., are not an issue. A future (e.g. 2004 or 2005) project might include the hours and the time difference between say January 23, 1968 at 20:31 and May 12, 2006 at 11:06 might be necessary. Today, though, only the difference (in Years, Months, and Days) between say January 12, 1475 and December 1, 3006 is necessary. (Again the two dates may both change (e.g. different dates from the same row) when running a table of people (to show their age or when something they own expires.)

I'm giving this 95 points (while it isn't all I have) it is over half of them. I noticed the point value chart says 125 is a not important question but moderately difficult. Sorry, if 95 points isn't a lot, but I hope someone will work on this as it has me stumped. There are so many date rules about leap year, century leap years, changes in the calendar, 1,000 year leap year rules, etc. I have some ideas and have had some moderate success, but nothing to take care of the many situations of years and tables.

I also did a Google search but didn't really find anything along this line. Thanks for you help.

P.S. Here are some examples using April 18, 2003 as the later of the two dates:

117 Years 11 Months 16 Days - May 02, 1885 A.D.

0 Years 2 Months 25 Days - January 24, 2003 A.D.

399 Years 5 Months 6 Days - November 12, 1603 A.D.

278 Years 3 Months 1 Days - January 17, 1725 A.D.

226 Years 9 Months 14 Days - July 04, 1776 A.D.

938 Years 3 Months 17 Days - January 01, 1065 A.D.

3778 Years 9 Months 14 Days - July 04, 1776 B.C.

ASKER CERTIFIED SOLUTION

bmoshier

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bertbear

ASKER

Looks good and the B.C. isn't that important. Can't you just add A.D. to the YYYY in the SQL code? For this project, all the dates are in the 20th or 21st century, so A.D. only is not a problem.

I'm going to leave the question open to see what others have, who knows maybe there is something better.

bmoshier

Awakefan

What about the following SQL:

To get the total number of months:
select months_between(date1, date2) as months
from some_table;

To get the number of years:
select trunc(months_between(date1, date2)/12) as years
from some_table;

To get the years and months:
select trunc(months_between(date1, date2)/12) as years
, months_between(date1, date2) - trunc(months_between(date1, date2)/12) as months
from some_table;

To get the years, months and remaining days:
select trunc(months_between(date1, date2)/12) as years
, months_between(date1, date2) - trunc(months_between(date1, date2)/12) as months
, date2 - add_months(least(date1, date2),months_between(greatest(date1, date2),least(date1,date2)) as days
from some_table;

Sorry, I cannot test it right now. But if it does not work, please say so.
from some_table;

bertbear

ASKER

Awakefan,

Interesting direction on your SQL code, but I couldn't really get it to work. Oh, I got it to parse and execute but the math just didn't come out correct - especially the days. They were way off (one of the dates I tested gave me: 0 years 1.23885 months and -277 days).

I'd have tried harder to get it to work (as the concept looks interesting) but the article by bmoshier works just fine for what I need.

I'm just wondering if something better than his sub-selects exists, but why fix what isn't broken - I guess. I was just thinking of performance. Maybe the sub-query/sub-selects (which is it?) isn't bad for performance. One thing is the math isn't done more than once, he passes just the answer up each time.

I liked your "automatic" way of determining the older of the two dates using the greater than or less than. His method makes me code the order of subtraction (but then so did my school teachers).

Thanks!

bertbear

ASKER

Thanks, you might want to post the article here (or the "core") in case your link doesn't work ... but what matters to me is I'm functional and my boss is happy.

Awakefan

Just couldn't leave it alone and had to make a single query that would do the trick. Just got access to a SQL*Plus and completed the query.

Here it is withou time (only dates):
select to_date('18-APR-2003','DD-MON-YYYY') as d1
, to_date('02-MAY-1885','DD-MON-YYYY') as d2
, months_between(greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
) as full_months
, trunc(months_between(greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
)/12
) as years
, trunc(months_between(greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
)
-
trunc(months_between(greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
)/12
) * 12
) as months
, greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY'))
-
add_months(least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, months_between(greatest(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
, least(to_date('18-APR-2003','DD-MON-YYYY')
, to_date('02-MAY-1885','DD-MON-YYYY')
)
)
) as days
from dual
/