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.
