March 6, 2008 at 7:14 pm
The best function to use for calculating age is:
DATEDIFF(yy, @DateOfBirth, GETDATE()) -
CASE WHEN (DATEPART(m, @DateOfBirth) > DATEPART(m, GETDATE()))
OR(DATEPART(m, @DateOfBirth) = DATEPART(m, GETDATE())
AND DATEPART(d, @DateOfBirth) > DATEPART(d, GETDATE()))
THEN 1 ELSE 0 END
All of the options given in the question contained an error. DateDiff with the yy option only calcualtes the difference in the year parts of the two dates. But if the dateofbirth came later in the year than than the current date, then this will add an extra year to the age. A date comes later in the year if its month is later than the other date's month, or if the two dates have the same month and the day of the first date is later than the day of the second date.
March 6, 2008 at 8:45 pm
I have here sample of the three queries. It seems like the Correct answer that was given is incorrect. My sample is '1985-02-04' the result should be 23 but it gives me 22. What went wrong?
Correct Answer
select DATEDIFF(yy, '1985-02-04', GETDATE()) -
CASE WHEN DATEPART(m, '1985-02-04') >= DATEPART(m, GETDATE())
AND DATEPART(d, '1985-02-04') >= DATEPART(d, GETDATE())
THEN 0 ELSE 1 END
Result : 22
Wrong Answer(s)
1. select FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, '1985-02-04', GETDATE())) / 365.0)
Result : 23
2. select DATEDIFF(yy, '1985-02-04', GETDATE())
Result: 23
March 7, 2008 at 2:20 am
And is this the best way to calculate age? I'm not so sure... How about converting the date to seconds and calculate from there? I think performance wise this should be faster.
March 7, 2008 at 2:29 am
Yes The 1 and the 0 are definitely the wrong way round, leap year's are a problem, but try using cast(DateDiff("d", DateOfBirth, getdate()) / 365.25 as tinyint) which is the method I normally use.
March 7, 2008 at 2:44 am
Frank Kalis has some interesting pages on the DATETIME type.
Look at page 5 for some birthday sql:
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p5.aspx
And on page 6 there is this nice one:
SELECT (0+CONVERT(CHAR(8),GETDATE(), 112) - CONVERT(CHAR(8), @d, 112))/10000
(Didn't test it on a leap year, but looking at the way it works this must be correct)
March 7, 2008 at 2:51 am
Colin Davidson (3/7/2008)
Yes The 1 and the 0 are definitely the wrong way round, leap year's are a problem, but try using cast(DateDiff("d", DateOfBirth, getdate()) / 365.25 as tinyint) which is the method I normally use.
Hi Colin,
That one is incorrect as well. For someone who is exactly one, two or three years old in a period that doesn't include a leap year, it will be one day off. For instance, someone born today (march 7 in my time zone) will be one year on march 7 2009 - but since that's 365 days, your formula will result in an age of 0 years.
The only correct methods I have seen in this thread so far are:
1) use datediff to get number of year boundaries passed, then subtract one if this year's birthday is still in the future (can be tested in several ways, just not in the way proposed in the third QotD answer)
2) use datediff to get number of month boundaries passed, then subtract one if day of birthday exceed day of current date, then divide by 12 and round down.
3) the (admittedly quite hackish) method from Frank Kalis' pages that Henk Schreij just posted a pointer to while I was composing my reply.
All other solutions proposed (and I have seen some very creative ideas) will fail for some combinations of dates.
(edit - added third correct method that was posted when I composed first version of my reply)
March 7, 2008 at 3:16 am
Your right, but for the applications i use it for i.e. checking whether someone is the correct age to apply for insurance policies when contacting us my code is sufficient, because no matter how accurate you are there is always a gap between sending out documents and receiving back the signed copy.
March 7, 2008 at 3:58 am
just posting that neither of the options given is the best way to calculate the age, as it's not the fastest way
doing YEAR(getdate() - Birthdate - 1) - 1900 is faster
small test prog if you want to try it out:
declare @date datetime
declare @i int
declare @start datetime
declare @age tinyint
SELECT @start = getdate(),
@i =0,
@date = '1976-12-21'
while @i < 1000000
begin
select @age = YEAR(getdate() - @date - 1) - 1900, -- DATEDIFF(yy, @date, GETDATE()) - CASE WHEN DATEPART(m, @date) >= DATEPART(m, GETDATE()) AND DATEPART(d, @date) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
@i = @i + 1
end
PRINT 'Duration: ' + CONVERT(VARCHAR,DATEDIFF(ms,@start,getdate())) + ' ms'
March 7, 2008 at 4:34 am
I also ran the information and found an error with the third choice. :hehe:
Try this in query anaylzer....
declare @dateofbirth datetime
select @dateofbirth = '02/24/1964'
print convert(varchar(20), @dateofbirth)
print convert(varchar(20), DATEDIFF(yy, @DateOfBirth, GETDATE()) )
print convert(varchar(20), FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0))
print convert(varchar(20), DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END )
March 7, 2008 at 5:31 am
[font="Arial"]Consider the following use of the specified "correct answer"[/font]
declare @DateOfBirth datetime
select @DateOfBirth = '2000-03-06'
select getdate() as [Today],
@DateOfBirth as [DateOfBirth],
DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END as [Age]
[font="Arial"]This returns 7, but obviously the age should be 8.[/font]
Today DateOfBirth Age
2008-03-07 07:27:14.527 2000-03-06 00:00:00.000 7
(1 row(s) affected)
[font="Arial"]The error occurs by not considering when the DateOfBirth month is greater than the current month but the DateOfBirth day is less than the current day.
The answer to this question of the day is "none of the above."[/font]
March 7, 2008 at 5:52 am
I couldn't get any of answers to work 100% of the time...
Here is what I came up with...
declare @dateofbirth datetime
select @dateofbirth = '03/08/1962'
select DATEDIFF(yy, @dateofbirth, GETDATE())
- CASE WHEN DATEPART(m, @dateofbirth) > DATEPART(m, GETDATE()) THEN 1
WHEN DATEPART(m, @dateofbirth) < DATEPART(m, GETDATE()) THEN 0
WHEN (DATEPART(m, @dateofbirth) = DATEPART(m, GETDATE()) AND DATEPART(d, @dateofbirth) <= DATEPART(d, GETDATE())) THEN 0
ELSE 1 END
...you only need to look at the day when the birthday is in the same month as the current date.
If it was easy, everybody would be doing it!;)
March 7, 2008 at 6:12 am
I wrote a function for this:
http://www.wisesoft.co.uk/Articles/SQL%20Server/fAgeCalc.aspx
DBA Dash - Free, open source monitoring for SQL Server
March 7, 2008 at 6:26 am
Ok,
YEAR(getdate() - Birthdate - 1) - 1900
seems to be marginally faster, however - you will face the leap-year problem...
declare @date datetime
declare @Today datetime
SELECT @date = '2000-03-01', @Today = '2007-03-01'
select YEAR(@Today - @date - 1) - 1900
Returns 6 years (should be 7)...
March 7, 2008 at 6:40 am
So, based on all the comments so far, does everyone agree we all should get our 2 points?
Perhaps a poll should be setup.
😎
March 7, 2008 at 6:52 am
Lynn Pettis (3/7/2008)
So, based on all the comments so far, does everyone agree we all should get our 2 points?Perhaps a poll should be setup.
😎
I'd rather say that noone who answered the question should get any points. After all, whatever you answered, you got it wrong 😛
Only those who were able to see that all queries are wrong and hence didn't answer the QotD at all should get any points.
Then again, maybe I'm biased... :Whistling:
(still waiting for a correct answer to be added so that I can finally get back to my track record of 100% of all QotD's attempted)
Viewing 15 posts - 76 through 90 (of 189 total)
You must be logged in to reply to this topic. Login to reply