sql server - 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