Friday, October 3, 2008

Best Way to Calculate Age

Given a "date of birth" value as a datetime type, what is the best way to calculate the current age (in years, as a tinyint value)?

Choose your answer:
Ans 2

The first answer, "DATEDIFF(yy, DateOfBirth, GETDATE())", is very efficient and readable, but what you have to remember about the DATEDIFF() function is that it first parses the specified DATEPART() from each value, then takes the difference of those. The incorrect expectation is to think it calculates the difference of the values first (to the highest precision available), and then returns that difference as specified by the DATEPART. So the first answer would actually return the person's age at the end of the year. The second answer, "FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, DateOfBirth, GETDATE())) / 365.0)", is far more accurate, because it calculates the difference in days and then divides by 365. This is roughly correct, but doesn't account for leap years, and thus might be off by a handful of days. The third answer first gets the difference in years, then subtracts 1 if the person has not yet had a birthday this year. This answer is 100% accurate, and is the correct response.

No comments: