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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- hud u s department of housing and urban
- purpose university of washington
- calculating age ac transit
- department of atomic energy
- exhibit 5 3 acceptable forms of verification
- youth and 4 h
- why is it important to weigh and measure infants children
- epi info 7 user guide chapter 3 check code
- function to calculate a persons age in t sql
Related searches
- how to calculate a mortgage payment formula
- how to calculate a mortgage payment
- how to calculate a payment
- how to calculate a loan payment
- how to calculate a bond
- how to calculate a p value
- how to calculate a car loan
- how to calculate a car payment formula
- how to calculate a savings bond
- how to calculate a garnishment
- how to calculate a loan
- how to calculate a car payment loan