Dennis Wagner-347763 (1/16/2014)
The issue with doing simple DATEDIFF in SQL is that it does a straight subtraction. From the example above:DATEDIFF(year, '10/16/95', '1/15/14') = 19 (but only 18 full years elapsed)
DATEDIFF(month, '10/16/95', '1/15/14')%12 = 3 (but only 2 full months elapsed)
Days is a real mess.
How about this: DATEDIFF(YEAR, '12/31/13', '1/1/14') = 1! (not the factorial) when only 1 day has elapsed
Yes, I do see what you mean. See my comment below for a better suggestion.
When comparing who is older when they achieved their Super Duper Fantastical Grandmaster Status, you have to use number of days because it is not affected by leap years, days in a month, etc.
So if person A above was compared to person B who was born December 14, 1995 and achieved this noteworthy status on March 15, 2014, then both of them would be 6,666 days old:
DATEDIFF(day, '10/16/95', '1/15/14') = DATEDIFF(day, '12/14/95', '3/15/14') = 6,666
However, when formatted as YMD, person A is 18 years, 2 months, 30 days while person B is 18 years, 3 months, 1 day, which would make person A seem younger. My original response states why you can't just fix 30 days in a month or you could end up with x years, 12 months, and 4 days when 364 days elapsed from the previous year anniversary.
I always use days when figuring out a "top 10" list, but I always format the answer as YMD so the age is meaningful to the audience.
I was a bit rushed when I responded earlier, and didn't fully communicate what I had in mind when I said, "Which years in the calendar should we use to calculate this?" The solution seems to be to calculate the days for comparison purposes. Then, add the days to a standard reference date, such as 1900-01-01. THEN do your DATEDIFF calculations. That way your 6,666 days will always work out to the same number of years, months & days, no matter what period of time they actually occurred over. The leaps years and months of variable length will always fall in the same spots, so days added to the reference date will always give a determinate result, which I believe is what you require.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn