Function to calculate a persons Age in T-SQL



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

-- *

-- * 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)day(@Date)

then datediff(yyyy,@DOB,@Date)-1

else datediff(yyyy,@DOB,@Date) end

end)

end

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download