June 30, 2008 at 12:21 pm
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?
June 30, 2008 at 1:21 pm
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?
😎
June 30, 2008 at 1:29 pm
1d, 1w, 1m, and 1y
thanks for any help you can give!
June 30, 2008 at 2:09 pm
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
😎
June 30, 2008 at 2:27 pm
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!
June 30, 2008 at 3:58 pm
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.
😎
July 1, 2008 at 12:20 am
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
July 1, 2008 at 2:06 am
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
July 1, 2008 at 3:10 am
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)
July 1, 2008 at 4:40 am
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
July 1, 2008 at 6:27 am
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)
July 1, 2008 at 6:56 am
Lynn Pettis (6/30/2008)
Peso (6/30/2008)
http://www.sqlteam.com/article/datediff-function-demystifiedPeter,
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."
July 1, 2008 at 7:12 am
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.
😎
July 4, 2008 at 1:49 pm
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
July 4, 2008 at 1:52 pm
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)
You must be logged in to reply to this topic. Login to reply