Finding someone's age in SQL

Question

In a SQL Server database, I record people's date of birth. Is there an straight-forward method of working out the person's age on a given date using SQL only?

Using DATEDIFF(YEAR, DateOfBirth, GETDATE()) does not work as this only looks at the year part of the date. For example DATEDIFF(YEAR, '31 December 2007', '01 January 2008') returns 1.

Answer 1

Check out this article: How to calculate age of a person using SQL codes

Here is the code from the article:

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = '20070210', @BirthDate = '19790519'

SELECT DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END

Answer 2

There is another way that is a bit simpler:

Select CAST(DATEDIFF(hh, [birthdate], GETDATE()) / 8766 AS int) AS Age

Because the rounding here is very granular, this is almost perfectly accurate. The exceptions are so convoluted that they are almost humorous: every fourth year the age returned will be one year too young if we A) ask for the age before 6:00 AM, B) on the person's birthday and C) their birthday is after February 28th. In my setting, this is a perfectly acceptable compromise.