SQL Server–Calculate Age with DateDiff/Rounding

Posted: 01/25/2011

One requirement for a reporting database might be to calculate the age of an individual to both ease report writers having to query birth dates frequently and/or the need to mask or remove birth date for security reasons (in the reporting environment, clearly the date would still be needed to recalculate the age on the data loads). The DateDiff can come in handy for calculating the age. However though, when using the DateDiff it will return a value that has been rounded up in the case of years, so individuals who are older than 32 but not yet 33 will have 33 as a value. The easy fix? Use the DateDiff function, have it return months instead of years, divide that by 12 and then round down (it should round down by default). 32.66 would then return 32 as the correct age.

datediff(mm,p.birth_date,getdate()) / 12  as age