# Calculating Age

• Well, they're vague because healthcare is so vague. 🙂 Different health providers have different needs. But let's say that under 1 month old should show age in days; between 1 month and 3 months should age in weeks; between 3 months and 1 year should show age in months. Does that make sense?

• Now, how would you like to see the result returned? If it is a single number value, how would you distinguish between 1 day, 1 week, 1 month, 1 year?

😎

• 1d, 1w, 1m, and 1y

• Start with this and do a lot of testing. You may need to tweak it as I did not do a lot of testing so its accuracy may vary.

`declare @dob datetime,`

` @age int,`

` @day datetime`

`set @day = '2008-12-15'`

`set @dob = '2007-02-28'`

`set @age = datediff(yy,@dob,@day) -`

` case when @day < dateadd(yy, datediff(yy,@dob,@day), @dob) then 1 else 0 end`

`select`

` case`

` when @day < dateadd(mm, 1, @dob)`

` then cast(datediff(dd, @dob, @day) as varchar) + 'd'`

` when @day < dateadd(mm, 3, @dob)`

` then cast(datediff(wk, @dob, @day) as varchar) + 'w'`

` when @day <= dateadd(yy, 1, @dob)`

` then cast(datediff(mm, @dob, @day) as varchar) + 'm'`

` else cast(datediff(yy,@dob,@day) -`

` case when @day < dateadd(yy, datediff(yy,@dob,@day), @dob) then 1 else 0 end as varchar) + 'y'`

` end`

😎

• VERY cool! And I can easily modify it to work in my queries, and even adjust some things for different care providers' needs!

Thank you!

• Sergio Lugo (6/30/2008)

Hi everyone !

I use this formula:

Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))

You can also put it in a calculated column.

It works (I think) because one year actually has 365.25 days... Like we only count 365 days in a regular year, we need compensate with an extra day every four years (leap year).

Try it and let me know how it goes !

Doesn't always work. Try the following:

`declare @dob datetime, -- to replace [DateOfBirth] in original query`

` @day datetime, -- instead of getdate() to allow testing different dates`

` @Age int;`

`--Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))`

`set @dob = '2000-03-01';`

`set @day = '2007-03-01';`

`set @Age = floor(datediff(day, @dob, @day)/(365.25))`

`select @Age`

On my system the result for the above query is 6 when it should be 7. I also changed the year to 2006 and 2005, and those were off by 1 as well.

😎

• Previous script returning a string for days when age < 1 month wks 1-3 months and months < 1 year has an out by one error on the months. in the last month of the year it returns 12 months. I guess this could be a cultural/geographic issue like the Chinese consider themeselves 1 year old from the moment they are born.

The author did say requires testing.

PS. I use the simple version that can be out by a day here and there but i am counting tens of thousands of kids and there would only be a few that go into the wrong age group. I have changed my stored procedure to include the sample given in this article embeded in a select statement and grouped on associated age column and all works fine. Cheers

• The safe way to calculate age:

`declare @d1 datetime, @d2 datetime;`

`select @d1= '2000-03-01', @d2='2007-03-01';`

`select floor((cast(convert(varchar(8), @d2, 112) as int) - cast(convert(varchar(8), @d1, 112) as int))/10000) age`

• Lynn Pettis (6/30/2008)

Sergio Lugo (6/30/2008)

Hi everyone !

I use this formula:

Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))

You can also put it in a calculated column.

It works (I think) because one year actually has 365.25 days... Like we only count 365 days in a regular year, we need compensate with an extra day every four years (leap year).

Try it and let me know how it goes !

Doesn't always work. Try the following:

`declare @dob datetime, -- to replace [DateOfBirth] in original query`

` @day datetime, -- instead of getdate() to allow testing different dates`

` @Age int;`

`--Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))`

`set @dob = '2000-03-01';`

`set @day = '2007-03-01';`

`set @Age = floor(datediff(day, @dob, @day)/(365.25))`

`select @Age`

On my system the result for the above query is 6 when it should be 7. I also changed the year to 2006 and 2005, and those were off by 1 as well.

😎

I have always found that when working with the datediff function a person have to be creative. I like the idea of using the day datepart in datediff and then do a calculation as below

"Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))"

Most of the times you have to add 1 (+1) at the end to get the real age of two dates. Hope this makes sense!

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

• One possible implementation for calculating "age" in day/week/month and year...

`declare @d1 datetime, @d2 datetime;`

`select @d1= '2004-02-29', @d2='2005-02-28';`

`select`

` datediff(dd, @d1, @d2) age_days`

`, floor(datediff(dd, @d1, @d2)/7) age_weeks`

`, datediff(mm, @d1, @d2) - case when datepart(dd, @d1)>datepart(dd,@d2) then 1 else 0 end age_months`

`, floor((cast(convert(varchar(8), @d2, 112) as int) - cast(convert(varchar(8), @d1, 112) as int))/10000) age_years`

• CREATE FUNCTION [dbo].[HowManyYears] (@Lo DateTime, @Hi DateTime) RETURNS integer

BEGIN

RETURN year(@Hi)-year(@Lo)+case when (100*(month(@Hi)-month(@Lo))+day(@Hi)-day(@Lo))<0 then -1 else 0 end

END

I offer the above as what is known in the UK as your "Starter for ten" since it has not been "Professionalized" as per Jeff Moden's most admirable strictures.

I would say that how it works is reasonably self-evident.

I would say that the 200+ messages on this topic are a pretty sad reflection of the state of the DBA art

and pretty obvious reason why MS should have included such a function in SQL Server from Day 1.

For those who still persist with the "divide by 365.25" ... it does not work properly on boundary conditions ... (which is when you really need it to)

• Lynn Pettis (6/30/2008)

Peter,

I just took the time to read your article above. I wish I had know about it as I was doing my article, I would have put a link to it in mine so that others could get more information about DATEDIFF and date arithmetic. Very well done and helpful.

😎

Very good complimentary article, the only comment I would have is that the explanation is muddied somewhat by including the date of the day in your explanation sets (e.g. {September 18; October 18; etc} when counting months).

I think it may be easier to just think of datediff as using the datepart solely as the basis for the arithmetic. (i.e. extract that datepart from both dates and compare) Therefore, having to decide if the first date is earlier or later in the month becomes a moot point. June 15 vs July 1 is June vs July, not June 15 vs July 15.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• Andrew Hall (7/1/2008)

Previous script returning a string for days when age < 1 month wks 1-3 months and months < 1 year has an out by one error on the months. in the last month of the year it returns 12 months. I guess this could be a cultural/geographic issue like the Chinese consider themeselves 1 year old from the moment they are born.

The author did say requires testing.

PS. I use the simple version that can be out by a day here and there but i am counting tens of thousands of kids and there would only be a few that go into the wrong age group. I have changed my stored procedure to include the sample given in this article embeded in a select statement and grouped on associated age column and all works fine. Cheers

Yes, I did say it needed testing. What I was really trying to illustrate with the code at that time was one way the determine how to compute an age and return different values dependent on the age: days, weeks, months, years. At this point, I won't vouch for the accuracy of the data returned, but proper testing and modification of the code will make it viable.

There appears to be a set of calculations also in this thread now that may be used as well. I have not tested them extensively to see if they are accurate, but the year calculation seems to work on at least the border test of 2000-03-01 (dob) to 2007-03-01 (date to calculate).

The purpose of my article was to show one way to calculate age, and to stimulate some additional discussion that could help identify other valid methods as well.

There is no one correct way, and sometimes, legal requirements may change how calculations are made. This meaning on when does a governmental organization determine when an individual born on February 29th become another year older. This was not a consideration when writing my article (I made the assumption that the became another year older on February 28th, keeping thier birthday in the same month).

Some good stuff in this thread, and I look forward to seeing some other and inventive methods in the future, either in this thread or perhaps someone else will write another article describing an different method and how it works.

😎

• Would just like to add that it is much easier just dividing the number of days by 365.25. Calculate day difference using datedff, dividy by 365.25 and discard the remainder.

Martin

• Actually when you are doing that you have to take the difference between the birthdate and the day before you wish to calculate to.

Martin

Viewing 15 posts - 16 through 30 (of 93 total)