Function to calculate a persons Age in T-SQL
About
This function will calculate a persons age at a specified
date. Two parameters are required,
@DOB for the persons date of birth and @Date for the
date at which to calculate the persons age.
You can use the GetDate() function for the second parameter to return a persons
current age.
SQL Server includes a datediff function that you can
use to calculate the number of days, months, years etc between two dates, but this
function can’t be used alone to calculate a persons age.
If you return the number of years from the datediff
function, your Age calculation could be 1 year out.
An alternative method would be to return the number of days between the date
and the person’s date of birth, divide by 365.25 and use the floor function to round
down. This method comes close, but
it’s not 100% accurate. The method
below checks the month (and day if required) of the date of birth compared to the
specified date then uses the datediff
function to find the number of years between the two dates, adjusting it
if required.
T-SQL Code
create
function dbo.fAgeCalc(@DOB datetime,@Date
datetime)
returns
smallint
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 03/11/2006
-- * http://www.wisesoft.co.uk
-- * This function calculates a persons age at a
-- * specified date from their date of birth.
-- * Usage:
-- * select dbo.fAgeCalc('1982-04-18',GetDate())
-- * select dbo.fAgeCalc('1982-04-18','2006-11-03')
----------------------------------------------------
begin
return
(
select
case when month(@DOB)>month(@Date)
then datediff(yyyy,@DOB,@Date)-1
when month(@DOB)<month(@Date)
then datediff(yyyy,@DOB,@Date)
when month(@DOB)=month(@Date)
then
case
when day(@DOB)>day(@Date)
then
datediff(yyyy,@DOB,@Date)-1
else
datediff(yyyy,@DOB,@Date)
end
end)
end