March 6, 2008 at 9:57 am
I agree with SSC Eights for the third solution!!!!!
Here it is:
SELECT 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
March 6, 2008 at 9:57 am
The third way is wrong for my birthday, which is 08/30/1954.
As of today I'm 53 years old, but it returned 54.
So definitely second way of age calculation is the best!
March 6, 2008 at 10:01 am
I want my point back too....
Here is the code as run with results:
declare @DateOfBirth datetime
set @DateOfBirth = '1959-08-29 09:59:00.000'
select DATEDIFF(yy, @DateOfBirth, GETDATE()) union all
select FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0) union all
select DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
set @DateOfBirth = '1959-01-29 09:59:00.000'
select DATEDIFF(yy, @DateOfBirth, GETDATE()) union all
select FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0) union all
select DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
@DateOfBirth = '1959-08-29 09:59:00.000'
---------------------------------------
49
48
49
(3 row(s) affected)
@DateOfBirth = '1959-01-29 09:59:00.000'
---------------------------------------
49
49
48
(3 row(s) affected)
😎
March 6, 2008 at 10:04 am
It should've been:
SELECT DATEDIFF(yy, 'DOB', GETDATE()) -
CASE WHEN DATEPART(m, 'DOB') >= DATEPART(m, GETDATE())
AND DATEPART(d, 'DOB') > DATEPART(d, GETDATE())
THEN 1 WHEN DATEPART(m, 'DOB') >= DATEPART(m, GETDATE())
AND DATEPART(d, 'DOB') >= DATEPART(d, GETDATE()) THEN 0 ELSE 0 END
All of it was wrong.. the case, the >= signs.. everything
March 6, 2008 at 10:05 am
The "best way" was not the accurate way. I ran all three of the options and my own age calculation function. I checked the accuracy of all three and only option 2 was closest, but it was not accurate all of the time. Once I switched the 365.0 to 365.25, option 2 did return the correct answers.
March 6, 2008 at 10:11 am
DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
is incorrect, it should be:
DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) > DATEPART(d, GETDATE()) THEN 1 ELSE 0 END
March 6, 2008 at 10:11 am
The third query has the 1 and 0 the wrong way round in the ELSE THEN part. my DOB is 9/6/66 and I'm not 42!! yet!!
March 6, 2008 at 10:15 am
Here is some code that works:
declare @DateOfBirth datetime
set @DateOfBirth = '1959-08-29 09:59:00.000'
select datediff(yy, @DateOfBirth, getdate()) - case when dateadd(yy,datediff(yy, @DateOfBirth, getdate()), @DateOfBirth) > getdate() then 1 else 0 end
set @DateOfBirth = '1959-01-29 09:59:00.000'
select datediff(yy, @DateOfBirth, getdate()) - case when dateadd(yy,datediff(yy, @DateOfBirth, getdate()), @DateOfBirth) > getdate() then 1 else 0 end
😎
March 6, 2008 at 10:17 am
I don't agree with question options. For my DOB I get right answer with #3. But it is not the best way.
I can define age as:
DATEDIFF(yy, 0, DATEDIFF(dd, DateOfBirth, GETDATE()))
On simple question should be simple answer.
March 6, 2008 at 10:29 am
All of the answers are flawed
Answer 1 (intentionally wrong): only calculates the value in difference between the 2 years (As BOL states)
Answer 2 (intentionally wrong): doesn't take into account leap years.
Answer 3 (supposedly correct): tries to subtract 1 from the original calculation given in Answer 1, but fails as it checks to see if the month value AND the day value are greater than or equal to the current day, however when you run this today(6 March) for a date of birth of 1 Apr, then 1>=6 AND 4(Apr) >=3(Mar) = FALSE.
what should be checked is a combination of month and day, i.e. (4>3) OR (4=3 AND 1>6)
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
also note that the 1 and 0 are transposed.
March 6, 2008 at 10:34 am
The same here. The second query provides the exact age rather the third provides the age based on the year. If DOB is 15-04-1986 & the present date is 06-03-2008, the age will be almost 22 but not 22. The age will still be 21 & the query-2 gives the result expected & not the 1st & 3rd.
Please let me know I am incorrect.
Thanks
Sudarshan
Cheers!
Sudarshan
March 6, 2008 at 10:55 am
I think Case condition has to be reversed and then we get the right answer
SELECT
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 6, 2008 at 10:59 am
I did not answer today's question since none of the answers is correct.
First, none of the answers returns a tinyint. One returns decimal(9,2), the others return int
Second, they all produce incorrect results, as can be see by the results of this script, which sets to March 7, 2007 (so I'll be celebrating my first birthday tomorrow - and still 0 years of age today)
DECLARE @DateOfBirth datetime;
DECLARE @getdate-2 datetime;
SET @DateOfBirth = '20070307';
SELECT GETDATE();
SELECT DATEDIFF(yy, @DateOfBirth, GETDATE());
SELECT FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0);
SELECT DATEDIFF(yy, @DateOfBirth, GETDATE())
- CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE())
AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE())
THEN 0 ELSE 1 END;
Results:
-----------------------
2008-03-06 11:49:48.500
-----------
1
---------------------------------------
1
-----------
1
Let me know when a fourth possiblility (either "none of the above" or a correct query) has been added. Until then, I'll have to pass on this QotD.
March 6, 2008 at 11:17 am
Agree with the fourth statement, it's the only one that returns a correct value.
March 6, 2008 at 11:32 am
/*
I was born on Nov/16/1957 therefore I am 50 years of age. However, the only correct statement, regardless the returning data-type, is the second one. So, I am not going to discuss any other aspect of this question of the day!!
*/
declare @DateOfBirth datetime
select @dateofbirth = '11/16/1957'
select DATEDIFF(yy, @DateOfBirth, GETDATE())
--
select FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0)
--
select DATEDIFF(yy, @DateOfBirth, GETDATE()) -
CASE
WHEN
DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE())
THEN 0
ELSE
1
END
-- thx
Cheers,
John Esraelo
Viewing 15 posts - 46 through 60 (of 189 total)
You must be logged in to reply to this topic. Login to reply