March 10, 2008 at 10:22 am
Jamie Longstreet (3/10/2008)
Hugo,Not sure what you mean but I took the date you implied (37 years and 9 months ago) and ran the routine and came up with 37 and 3/4 years which is correct.
select (dbo.JulianDay('3','10','2008')-dbo.JulianDay('6','10','1970'))
select 13788.0/365.2422
Jamie
Hi Jamie,
37 3/4 years is correct, but not a whole number. The QotD was to calculate age as an integer (and hence, whole years).
Use ROUND(...), and 37 3/4 years will be rounded up to 38 years, which is not what someone 37 years and 9 months old will quote as his or her age.
Use FLOOR(...) [or CAST(... AS int), or implicit conversion], and it will round down to 37, which is correct in this case - but now, the same formula will fail for someone 365 days old (unless 29 february happens to be among them).
March 10, 2008 at 10:26 am
1) I see the same result. I used to go to school with a woman who claimed she was only 4 because of her leap year birthday. 🙂 How old [age] does the US really consider someone like that?
2) 🙂
3) Yup, I chose only 200 years (!) to limit the number of rows being counted. (see #4 hehe)
4) I haven't tried yet, but I guess it'd be easy to create a calendar table, instead of a number table. Then it'd be a simple seek query, right ?
5) I chose months because there are always 12 months in a year, whether leap or not. (And days per month changes)
March 10, 2008 at 10:39 am
Rick Harker (3/10/2008)
1) I see the same result. I used to go to school with a woman who claimed she was only 4 because of her leap year birthday. 🙂 How old [age] does the US really consider someone like that?2) 🙂
3) Yup, I chose only 200 years (!) to limit the number of rows being counted. (see #4 hehe)
4) I haven't tried yet, but I guess it'd be easy to create a calendar table, instead of a number table. Then it'd be a simple seek query, right ?
5) I chose months because there are always 12 months in a year, whether leap or not. (And days per month changes)
Hi Rick,
1) I know little of US law (heck, I've only been in the US twice so far). But I'm willing to bet that people born on February 29th are considered to be 1 year old on March 1st the next year.
4) No. The COUNT(*) instead of MAX(something) is one reason why at least all rows up until the last matchhing one have to be processed (so that would be an index scan with a filter that allows it to stop once past the upper bound). But the more important problem is the non-sargable expression. This expression precludes the use of both an index seek and a limited scan - even with MAX() instead of COUNT(), SQL Server will still have to process all rows, since it has no way of knowing that there will be no matches once the first non-match has been found.
5) But there's also always 1 year in a year, whether leap or not - so why not choose years?
If you use years instead of months in the DATEADD and remove the "/ 12", you will get the exact same results for each test case (except that you can now calculate ages up to 2400 years old) - including the incorrect results for people born on a leap day.
March 10, 2008 at 10:42 am
I see Hugo's point. You cannot take into account leap years and other such anomalies when you are creating a calculation using months. Months are unreliable in a calculation that is based on using leap years.
One of Jesse's original requirements was that we use integers to get the date "((in years, as a tinyint value)?"
This is the right track because to be accurate you must count days and not months and years or you will fall into the trap that sometimes we have leap years and sometimes we do not. There is only one "best" solution and that is, you must count days. More than one was proposed that works but the ones that worked counted days and ignored Years and Months.
Jamie
March 10, 2008 at 11:00 am
There is only one "best" solution and that is, you must count days.
So when someone asks you how old you are, you count days before you answer? Nonsense.
The best answer is the straightforward one everyone uses: the number of times your birthdate, month and day, has occurred since you were born. So that's what my code did :).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 10, 2008 at 11:06 am
Jamie Longstreet (3/10/2008)
I see Hugo's point. You cannot take into account leap years and other such anomalies when you are creating a calculation using months. Months are unreliable in a calculation that is based on using leap years.One of Jesse's original requirements was that we use integers to get the date "((in years, as a tinyint value)?"
This is the right track because to be accurate you must count days and not months and years or you will fall into the trap that sometimes we have leap years and sometimes we do not. There is only one "best" solution and that is, you must count days. More than one was proposed that works but the ones that worked counted days and ignored Years and Months.
Hi Jamie,
Actually, you missed my point 🙁
You must NOT count days, since there is no (at least no easy) way to tell if 365 days should be considered 1 year or 0 years. And similar for 730 days, etc.
You CAN count months (if you use DATEDIFF instead of the DATEADD method Rick suggests), or you can even count years - but in both cases, you have to cater for the fact that SQL Server calculates month/year boundaries passed, not full months/years passed, so you may have to subtract one (as solution c in the QotD attepmts to do - and fails at).
Or you can use the ugly hack, posted off-site by Frank Kalis and pointed to by Henk Schreij in an earlier post. It's not a solution I'd use, though.
So te recap, these are the correct solutions:
1) DATEDIFF(year, ...) - but subtract one if birthday's month exceeds current moth, or if months are equal but birthday's day exceeds current day (this can be simplifed by comparing MMDD for both dates; I've seen various techniques for this posted)
2) one twelfth of DATEDIFF(month, ...) - but subtract one (before dividing!!) if birthday's day exceeds current day
3) Convert both dates to a YYYYMMDD string, then to an integer; subtract, then divide by 10000 and truncate (this is the ugly hack I mention before).
Every other solution I've seen so far produces incorrect results for at least SOME combinations of birthdate and current date.
March 10, 2008 at 12:28 pm
Here is a formula that appears to work okay in SQL Server:
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
Here is my tests:
declare @dob datetime,
@calcDate datetime
set @dob = '1996-02-29'
set @calcDate = '1997-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '1997-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2004-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2004-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2007-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2007-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @dob = '1959-08-29'
set @calcDate = getdate()
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2008-09-29'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @dob = '1759-08-29'
set @calcDate = getdate()
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2008-09-29'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
😎
March 10, 2008 at 12:41 pm
Lynn Pettis (3/10/2008)
Here is a formula that appears to work okay in SQL Server:
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
I'm sorry, Lynn, but this one is flawed as well.
declare @dob datetime,
@calcDate datetime
set @dob = '19950401'
set @calcDate = '19960331'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1 else 0 end as AgeInYears
returns 1 instead of 0. Because 1996 is a leap year, the dy value of march 31, 1996 is equal to that of april 1, 2007. This causes the formula to go wrong.
Whoever invented our current calendar clearly did not anticipate computers 😀
March 10, 2008 at 12:47 pm
I did say appears to work. That was the leap year test I was missing. thanks
😎
March 10, 2008 at 1:00 pm
How about this:
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
Tests:
declare @dob datetime,
@calcDate datetime
set @dob = '1996-02-29'
set @calcDate = '1997-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '1997-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2004-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2004-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2007-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2007-03-01'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @dob = '1959-08-29'
set @calcDate = getdate()
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2008-09-29'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @dob = '1759-08-29'
set @calcDate = getdate()
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @calcDate = '2008-09-29'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
set @dob = '1995-04-01'
set @calcDate = '1996-03-31'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears
😎
Edit: Don't answer, I already know, still have a problem. It appears I missed one of my earlier tests! Sorry.
March 10, 2008 at 1:01 pm
I just tested all three proposed functions, using my own date of birth, and it got the answer wrong in all three cases. I copied and pasted them into Management Studio, plugged in my birthday, and got the same wrong answer from all three.
If I substitute in 1 Jan 08 instead of Getdate(), the second one gets the right age, but the last one (the one that is "right" per the question), still gets it wrong.
The problem with it is in the Case statement. It assumes both the day and month will be >=, but that's not necessarily true. If someone is born on 14 March, and today is 8 March (to use Einstein's birthday as an example), the month is >=, but the day is <. So it doesn't subtract, and ends up with the wrong number.
Here's the function I recommend for this:
select datediff(yy, @dateofbirth, getdate()) -
case
when dateadd(year,
datediff(yy, @dateofbirth, getdate()),
@dateofbirth) > getdate() then 1
else 0
end
This takes the datediff, adds it back to the birthdate, and if that date is >=, then it subtracts 1. I tested this, and it worked for all the birthdates and current dates I tried it on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2008 at 1:08 pm
GSquared, don't think so. Please doupbe check this code snippet to be sure I have your code right:
declare @dob datetime,
@calcDate datetime
set @dob = '1996-02-29'
set @calcDate = '1997-02-28'
select
@dob DateOfBirth,
@calcDate CalcDate,
datediff(yy, @dob, @calcDate) - case when (@calcDate < dateadd(yy, datediff(yy, @dob, @calcDate), @dob)) then 1 else 0 end as AgeInYears,
datediff(yy, @dob, @calcDate) -
case
when dateadd(year,
datediff(yy, @dob, @calcDate),
@dob) > @calcDate then 1
else 0
end
Looks like it fails this test just like mine.
March 10, 2008 at 1:13 pm
GSquared (3/10/2008)
Here's the function I recommend for this:
select datediff(yy, @dateofbirth, getdate()) -
case
when dateadd(year,
datediff(yy, @dateofbirth, getdate()),
@dateofbirth) > getdate() then 1
else 0
end
This takes the datediff, adds it back to the birthdate, and if that date is >=, then it subtracts 1. I tested this, and it worked for all the birthdates and current dates I tried it on.
But it doesn't work on this combination:
declare @dateofbirth datetime,
@calcDate datetime
set @dateofbirth = '19960229'
set @calcDate = '19970228'
select datediff(yy, @dateofbirth, @calcDate) -
case
when dateadd(year,
datediff(yy, @dateofbirth, @calcDate),
@dateofbirth) > @calcDate then 1
else 0
end
If you take the time to browse through this thread (a daunting task by now, I know ;)), you'll find several solutions that do work correctly for all dates. This is not one of them....
(edit - Looks like Lynn beat me to the punch this time, posting with the exact same sample dates I used 😀 Well done, Lynn! :))
March 10, 2008 at 1:29 pm
If there is a way to do this without comparing months and day, I just can't find on my and I haven't the energy to search all these posts. So, I have a solution, and I am not posting all the tests, and this is what I have:
datediff(yy, @dob, @calcDate) - case when (month(@calcDate) < month(@dob) or (month(@calcDate) = month(@dob) and day(@calcDate) < day(@dob))) then 1 else 0 end,
This is probably like one or more of the correct methods somewhere in this thread, and if so, great. I'm done. I just don't think this question should count as two points attempted since none of the answers in the question were correct for all test values.
😎
March 10, 2008 at 1:46 pm
Lynn and Hugo:
I took Leap Years into account, and even (to be totally anal retentive about it), the hour, minute, second, and number of milliseconds, on each time.
Maybe I'm operating on the wrong standard here, but the people I have known who had Feb 29 birthdays (2 people, admitedly a very small sample), both celebrated their birthday on Feb 28 on non-leap years, not on March 1.
Thus, for example:
declare @dateofbirth datetime,
@calcDate datetime
set @dateofbirth = '19960229'
set @calcDate = '20000228'
select datediff(yy, @dateofbirth, @calcDate) -
case
when dateadd(year,
datediff(yy, @dateofbirth, @calcDate),
@dateofbirth) > @calcDate then 1
else 0
end
Where both dates are leap years, but the end date of the calculation is 28 Feb 2000, it correctly calculates it as 3 years.
If you go by a standard of "your birthday was 29 Feb, so your age is based on a birthdate of 1 March except in Leap Years", then, yes, this function is broken. Again, I'm basing my assumption on 2 people.
http://www.timeanddate.com/date/leap-day-february-29.html has more data on this subject. Per the data on that page, you'd actually have to accommodate local laws and regulations with regard to this point. In that case, yes, my function is broken for some. But the samples I've seen on this page that accommodate Leap Years the other direction (towards 1 March), are also broken. The page has an example about South Carolina having different rules than Maryland. To truly accommodate all such rules, a table of locations would have to be built and used in the function.
Based on that data, saying my function is more/less correct than a function that corrects in the other direction, is incorrect.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 136 through 150 (of 189 total)
You must be logged in to reply to this topic. Login to reply