If you test the calculation with real dates, you will find that the errors increase with increasing age.

For example: I am 62 years old. The calculation shows me to have been 62 years old, fifteen days before my 62nd birthday. This is not accurate enough for most legal calculations.

You can use DateDiff(yyyy, [dob} , [calcDate]) to do the calculation, however it will be off by one additional year if the date of the calculation is earlier in the year than the date of birth.

That is why we need to use such convoluted calculation logic.





Rand

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 this is included in the function script.

select @Age as AgeInYears, floor(@Age) AS AgeYear, cast(ROUND(((@Age-floor(@Age)) * @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


Marcel Crudele
innerEcho, Inc.

If your date_of_birth is in datetime format, just remove the casting.

CASE
              WHEN ((person.date_of_birth IS NULL) or (person.date_of_birth = ' ')) THEN NULL
              WHEN (cast(person.date_of_birth as datetime) > getdate()) THEN NULL
              WHEN DateADD(yyyy, DateDiff(yyyy, cast(person.date_of_birth as datetime), getdate()), cast(person.date_of_birth as datetime)) > getdate()
                 THEN DateDiff(yyyy, cast(person.date_of_birth as datetime), getdate()) - 1
                 ELSE DateDiff(yyyy, cast(person.date_of_birth as datetime), getdate())
           END as age

Scott Sherman

Hello

I wrote a complete application using Visual Basic 2005 Code to Calculate Age and Leap Years.

www.pace-med-apps.com/freeCode.aspx

Scott E Pace MD
www.pace-med-apps.com

Hi all,

I think u all are very intelligent persons, but why are u west time on a simple question about age calcualtion.

I think this query may be helpful for all u. and after it u have no dobt about age calculation.

Now look this query this will read DateofBirth field of Employee_Master table and Display DOB and Age in Year,Month & Days.

Select DOB,
    (case when Dys<0
    then
    (case when Mon-1<0 then Yr-1 else Yr end)
    else
    (case when Mon<0 then Yr-1 else Yr end)
    end) as Yr,
    (case when Dys<0
    then
    (case when Mon-1<0 then Mon-1+12 else Mon-1 end)
    else
    (case when Mon<0 then Mon+12 else Mon end)
    end) as Mon,
    (case when Dys<0
    then
    Dys+DateDiff(Day, DateAdd(Month,-1,GetDate()),GetDate() )
    else
    Dys
    end) as Dys
from
(
Select DOB,
    DateDiff(Year,DOB,GetDate()) as Yr,
    DateDiff(Month,DateAdd(Year,DateDiff(Year,DOB,GetD ate()),DOB),GetDate()) as Mon,
    DateDiff(Day,DateAdd(Month,DateDiff(Month,DOB,GetD ate()),DOB),GetDate()) as Dys
from Employee_Master
) tab


Please Check it on Different Cases if you find any mistake on calculation Age by this Query.

Thanks.

See http://www.sqlteam.com/article/dated...on-demystified

You might come up with a calculation that counts full year differences, basically pulling out the year say for example 2007 - 1960 and then add one if the current MM/DD is greater than the comparision MM/DD. For example:

02/15/1960 as a birthday, assume today is 03/15/2007
by subtracting the 2007 from the 1960 you get 47. But if the persons birtdate has not happend yet this year you would subtract one. Since 03/15 is after 02/15 this is correct but if it was 01/14/2007 the person would only be 46. This type of calcuation accounts for leap year accurately and should work 100% of the time as long as the dates are within sql servers ability to store the dates. Sql server won't be able to calculate from say 1600's. If you want to do these types of calculations you need to store the data as characters.

The SQL datetime field space is large enough to hold dates all the way back to well into the BC range, the only problem is handling the conversion between the Gregorian, Julian and pre-Julian calendars. That is why the minimum date in SQL Server is 01-Jan-1753. In 1752 the English speaking countries adopted the Gregorian calendar, therefore any date after 01-Jan-1753 can be assumed to be in the Gregorian calendar.

Russia did not adopt the Gregorian calendar until after the 1917 revolution. That explains why the October Revolution (Julian calendar) occurred in November (Gregorian calendar). Pre-revolutionary Russian dates can be as many as eleven days different from their Gregorian equivalent.

Rand

If you are desperate to make calculations on dates before or either side of the calendar switch then SQL Server 2008 has DateTime2 which copes with these. So you just have to wait a few months and persuade the department to upgrade :)

--

Joe (Microsoft MVP - XML)

My I. T. dept is very conservative and has a very limited budget. The ongoing joke is - will we manage to convert to SQL Server 2005 before SQL Server 2008 is finally released.

Rand