I just ran across this problem and was sure there would be a function out there to handle it, but couldn't find one. Just for the reusability of it, I wrote one for SQL 2005...
=======================================
How to calculate age in 2 easy steps…
=======================================
1. Run the following script in any SQL 2005 DB you want to determine Age in.
2. Calculate age anytime you want by sending a start date and end date in the format
select dbo.CalcAge(@StartDate, @EndDate)
The return value will be a decimal in the format of number of years old plus a decimal representing the number of days since the last anniversary date divided by the number of days until the next anniversary date – this does take into account leap years so be careful.
An example of how to break this information out as age in years and days since last anniversary is shown below (there is a precision issue, but ROUND seems to handle it). @NumDaysPerYear should be the number of days between last anniversary and next anniversary if you take into account leap year (365 or 366). The way to calculate @NumDaysPerYear is included in the function script.
select dbo.CalcAge(@StartDate, @EndDate) as AgeInYears, floor(dbo.CalcAge(@StartDate, @EndDate) ) AS AgeYear, cast(ROUND(((dbo.CalcAge(@StartDate, @EndDate) -floor(dbo.CalcAge(@StartDate, @EndDate) )) * @NumDaysPerYear),0) as smallint) As DaysSinceLastAnniversary
--===========================
-- RUN THIS SCRIPT TO
-- CREATE CalcAge Function
--============================
CREATE FUNCTION dbo.CalcAge (@StartDate datetime, @EndDate datetime)
RETURNS decimal(18,9)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Age decimal(18,9), @RecentAnniversary smalldatetime, @UpcomingAnniversary smalldatetime,
@NumDaysPerYear int
--SELECT @StartDate='10/18/1910', @EndDate='8/11/2000'
-- DETERMINE THE MOST RECENT ANNIVERSARY DATE AND NEXT ANNIVERSARY DATE.
-- THIS WILL BE USED FOR DETERMINING THE DECIMAL PART OF AGE.
select @RecentAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate) as varchar(4)) as smalldatetime)
if @RecentAnniversary>@EndDate
BEGIN
select @UpcomingAnniversary=@RecentAnniversary
select @RecentAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate)-1 as varchar(4)) as smalldatetime)
END
else
select @UpcomingAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate)+1 as varchar(4)) as smalldatetime)
-- NOW DETERMINE THE NUMBER OF DAYS BETWEEN THE ANNIVERSARY DATES. T
-- THIS WILL TAKE INTO ACCOUNT LEAP YEARS
select @NumDaysPerYear = DateDiff(day, @RecentAnniversary, @UpcomingAnniversary)
-- GET THE BASE AGE OF OF THE DATES
SELECT @Age=DATEDIFF (YYYY, @StartDate, @EndDate) -
CASE
WHEN (MONTH(@StartDate)=MONTH(@EndDate) AND DAY(@StartDate) > DAY(@EndDate)
OR MONTH (@StartDate) > MONTH (@EndDate))
THEN 1
ELSE 0
END
-- NOW GET THE DECIMAL PART
--select @Age, DateDiff(day, @RecentAnniversary, @EndDate), @NumDaysPerYear
Select @Age=@Age + cast(DateDiff(day, @RecentAnniversary, @EndDate) as decimal(12,9)) / cast(@NumDaysPerYear as decimal(12,9))
-- TEST BREAKING OUT COMPONENTS. THERE ARE SOME PRECISION ISSUES, BUT THE ROUNDING
-- BELOW SEEMS TO ADDRESS IT
--select @Age as Age, floor(@Age) AS AgeYear, cast(ROUND(((@Age-floor(@Age)) * @NumDaysPerYear),0) as smallint) As DaysSinceLastAnniversary
RETURN (@Age)
END;
GO
-- ================
-- END SCRIPT
-- ================
--Marcel Crudele
--innerEcho, Inc.