Common Forum Queries: Calculate age from the date of birth

Some questions reappear on forums more than others, my experience suggests none more so than how one should work out an individual’s age based on their date of birth.

I have been using the following logic within a procedure, as I didn’t require specific months and days as part of the returned result.



DECLARE @DOB as DateTime

Set @DOB='29 Jun 1980' -- Date Of Birth

select year(getdate())

- year(@DOB)

- case when month(@DOB) > month(getdate()) then 1

when month(@DOB) < month(getdate()) then 0 when day(@DOB) > day(getdate()) then 1

else 0

end as age



Well now the requirements have changed with the application now expecting the Month and Day part of the calculated age.

The following tsql will fulfil these new criteria:

DECLARE @date datetime,

@tmpdate datetime,

@years int,

@months int,

@days int

SELECT @date = '6/29/80'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN MONTH(@date) > MONTH(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years as Yr, @months as Mnths, @days as [Days]



This seems to do the trick but I’d be interested to hear from anyone with a different (better) way of producing the additional columns for the latter query.