Calculate Age Function in T-SQL


Create function dbo.FunAgeCalc(@DOB datetime)

returns smallint

as

begin

return (

select case when month(@DOB)>month(getdate()) then datediff(yyyy,@DOB,getdate())-1

when month(@DOB)<month(getdate()) then datediff(yyyy,@DOB,getdate())

when month(@DOB)=month(getdate()) then

case when day(@DOB)>day(getdate())

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

else datediff(yyyy,@DOB,getdate())

end

end

)

end

to use it:

select dbo.FunAgeCalc(CAST(‘2000-05-05 08:00:00.000’ As DateTime)) as Age