Dennis Wagner-347763 (1/16/2014)
Thomas, I like the way you are thinking. My solution was to build a function that would use the elapsed days to do the formatting.
While I agree that using a reference table would give a consistent determinate result, the issue would be how do we build that reference table.
That's why I'd use a reference DATE, instead of a table. No construction required.
We can't just use 30 days in a month, or we'll run out of months before we run out of days in a year.
There are applications where this IS done. Ever seen financial calculators for loans? They use standard 30 day months. But, that's a different animal I suppose.
The issue then becomes presentation and acceptance. If I compare someone born on February 15th with a date on March 16th, using a standard reference table that assumes the first month has 31 days, I should get x years, 0 months, and 29 days. While that answer is absolutely correct, the presentation can get sticky when someone looks at the birth date and the achievement date and argues that should be x years 1 month and 1 day. If you have to write more than 3 words to explain it on your presentation, you've lost your argument!
How it's presented is part of the issue when the audience requires that it be formatted in YMD. Going back to my original question though: I have a function that is somewhat messy to do the formatting. I was wondering if anyone had a simple elegant solution they've used to do the formatting.
Seems like you only have two choices here:
1. Recognize that there is no simple elegant solution when dealing with our calendar, which is neither simple nor elegant. This means you have to do a modified version of the three DATEDIFFs, which I imagine is what you are already doing, e.g. "years = DATEDIFF(yy, datea, dateb), less 1 if month and day of dateb falls before month and day of datea".
2. Make a single simplifying assumption (a reference date) that makes all time periods comparable, regardless of when they occur, realizing that there may be some disagreement with the results.
Both solutions have drawbacks. Which you choose depends on which drawbacks you find acceptable.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn