March 7, 2008 at 11:10 am
All three queries returned the wrong answer for me. My birthdate is this month and each returned an age one year older than actual. The third expression is only correct if the 1 and 0 in the case are switched. I don't mind trick questions, but at least one of the answers should be right!
March 7, 2008 at 12:10 pm
Let me weigh in with the observation that the 1 and 0 should be reversed on 3rd answer. [which by the way I determined before reading the comments.] Interesting exercise.
March 7, 2008 at 12:16 pm
Everyone saying the third option is correct if the case is switched needs to look at what is being checked.
What does the current day of the month have to do with the day of birth ? (Answer: nothing, If the months are not the same)
🙂
March 7, 2008 at 12:26 pm
Rick Harker (3/7/2008)
Everyone saying the third option is correct if the case is switched needs to look at what is being checked.What does the current day of the month have to do with the day of birth ? (Answer: nothing, If the months are not the same)
🙂
That sounds familiar....
Trader Sam (3/7/2008)
...you only need to look at the day when the birthday is in the same month as the current date.
Thanks Rick! At least one person agrees! 😀
(gosh, I hope its right) :Whistling:
If it was easy, everybody would be doing it!;)
March 7, 2008 at 12:29 pm
There sure has been a lot of traffic on this. This is what I think is an easy way to do this accurately.
select Case when Dateadd(yy,datediff(yy,DateOfBirth,getdate()),DateOfBirth) <= Getdate()
then datediff(yy,DateOfBirth,getdate())
else datediff(yy,DateOfBirth,getdate()) - 1
end
Q
Please take a number. Now serving emergency 1,203,894
March 7, 2008 at 12:43 pm
I admit I don't care for all the "uber-geeky" solutions; it's just too hard later for s/o later to verify that the code produces a correct result.
But leap year birthdays must be dealt with, of course, so I prefer this general approach:
SELECT
DATEDIFF(YEAR, DateOfBirth, CurrDate) -
CASE WHEN CONVERT(CHAR(5), DateOfBirth, 1) >
CONVERT(CHAR(5), CurrDate, 1) THEN 1 ELSE 0 END
--or, if it's clearer to you, ...CurrDate... < ...DateOfBirth...
as in:
DECLARE @currDate DATETIME
SET @currDate = '20070301'
SELECT DATEDIFF(YEAR, DateOfBirth, @currDate) -
CASE WHEN CONVERT(CHAR(5), DateOfBirth, 1) >
CONVERT(CHAR(5), @currDate, 1) THEN 1 ELSE 0 END
from (
select CAST('20040229' AS DATETIME) as DateOfBirth union all
select '20040301' union all
select '20040228' union all
select '20040302' union all
select '20040907'
) as testData
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2008 at 2:36 pm
Most people seem to be modifying the 3rd option to make it correct, but the best answer is what is the simplest, meets ALL the criteria (results in tinyint) and is always accurate.
----
DECLARE @dob datetime
SET @dob = '1967-12-09'
SELECT cast(DATEDIFF(d, @dob, GETDATE()) / 365.25 as tinyint)[My Age]
----
This works for all valid dates.
Louie
March 7, 2008 at 2:57 pm
louie.cowen (3/7/2008)
Most people seem to be modifying the 3rd option to make it correct, but the best answer is what is the simplest, meets ALL the criteria (results in tinyint) and is always accurate.----
DECLARE @dob datetime
SET @dob = '1967-12-09'
SELECT cast(DATEDIFF(d, @dob, GETDATE()) / 365.25 as tinyint)[My Age]
----
This works for all valid dates.
Louie
Hi Louie,
You obviously forgot to test your bicentennial birthday 🙂
DECLARE @dob datetime
SET @dob = '1967-12-09'
DECLARE @getdate-2 datetime -- Easier for testing
SET @getdate-2 = '2167-12-09'
SELECT cast(DATEDIFF(d, @dob, @getdate-2) / 365.25 as tinyint)[My Age]
Returns 199 instead of 200
March 7, 2008 at 3:38 pm
hmm, maybe I should have qualified what I meant by a valid date? :^D
I was going to argue that since most of us humans don't live past a max of 120 years, I kept my testing within that "reasonable" range. But, to my own dismay, I actually notice that the "/ 365.25" solution can see problems at the lower spectrum of ages too, like the first 10 years.
My testing:
DECLARE @dob datetime, @getdate-2 datetime, @days decimal(8,5)
SET @dob = '1968-03-08'
SET @getdate-2 = '2008-03-08'
SET @days = 365.25
SELECT
cast(DATEDIFF(d, @dob, @getdate-1) / @days as tinyint)[bday-1]
,cast(DATEDIFF(d, @dob, @getdate-2) / @days as tinyint)[bday]
,cast(DATEDIFF(d, @dob, @getdate-2+1) / @days as tinyint)[bday+1]
Good catch!
March 7, 2008 at 10:07 pm
I want my point! datatype or not, I can't pick the choice that doesn't return my correct age! 2 was the only one that worked for me, so I had to pick it!
March 8, 2008 at 12:18 pm
Reverse the values of 0 and 1 for the condition and else in the case statement and #3 is correct...
I'm sure I wasn't the first to notice this but I don't have the time to read all 12 pages of posts...
March 8, 2008 at 3:09 pm
Robert Price (3/8/2008)
Reverse the values of 0 and 1 for the condition and else in the case statement and #3 is correct...I'm sure I wasn't the first to notice this but I don't have the time to read all 12 pages of posts...
Hi Robert,
If you had invested that time, you'd have known that reversing the 0 and 1 values doesn't make the third solution any more correct. Here's a repro:
DECLARE @DateOfBirth datetime,
@getdate-2 datetime;
SET @DateOfBirth = '20070401';
SET @getdate-2 = '20080308';
SELECT DATEDIFF(yy, @DateOfBirth, @getdate-2)
- CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, @getdate-2)
AND DATEPART(d, @DateOfBirth) >= DATEPART(d, @getdate-2)
THEN 1 ELSE 0 END;
Returns 1 as the age someone born April 1st 2007 has today (March 8th 2008).
March 8, 2008 at 7:58 pm
Second option is correct answer.
Answer from option 1 and option 3 will be incorrect 98% times whereas chances of getting wrong answer from option 2 is not more than 2%.
Hence, none can provide correct answer all the time so most close answer is option 2.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 8, 2008 at 9:49 pm
During leap year only the second one works correctly. The third one will not work correctly if the current date is March 1st or later unless it is after a presons birthday.
March 9, 2008 at 1:36 am
Mohan Kumar (3/8/2008)
Hence, none can provide correct answer all the time so most close answer is option 2.
If you look back through the 12 pages of discussion, you'll see several solutions that are correct all the time.
I still believe that the question should be corrected (and I'm frankly quite surprised Steve hasn't reacted yet - he's ususlly quite quick to take corrective action when a QotD is proven to be wrong)
Viewing 15 posts - 106 through 120 (of 189 total)
You must be logged in to reply to this topic. Login to reply