March 6, 2008 at 8:45 am
This was an interesting question. I played with several dates and all I can say is that for each of the solutions, some work some of the time, but none all of the time.
I took the point that the case 1 and 0 appeared round the wrong way so corrected, then I put in 1960-04-01 and ran. The answer came back 48 for 1 and 3 (wrong!) but 2 was correct. I put in 1960-03-07 (i.e birthday tomorrow) and 1 and 2 were wrong (48 not 47) but 3 was right. For 1960-03-05, 1 and 3 were right (48) but 2 was wrong. I don't think the case statement holds up in 3. I think this works:
CASE WHEN DATEPART(m, @DoB) > DATEPART(m, GETDATE()) OR (DATEPART(m, @DoB) = DATEPART(m, GETDATE()) AND DATEPART(d, @DoB) >= DATEPART(d, GETDATE())) THEN 1 ELSE 0 END D3
March 6, 2008 at 8:45 am
Same here - the second statement is OK for me.
Mistake in the Qu.
March 6, 2008 at 8:47 am
The second answer is clearly the best answer. The third one is incorrect and the first one is clearly not the best. A better answer might have been to use a julian date routine as this is the method used by those who must look at ALL birthdata. The Julian date cannot be used in this case as the user suggested INTEGER as a method to obtain the date. This is a clear case where someone has placed a question without properly reseaching the results.
Jamie
March 6, 2008 at 8:50 am
The second answer is clearly the "best" answer. The third one is incorrect and the first one is clearly not the best. A better answer might have been to use a julian date routine as this is the method used by those who must look at ALL birthdata. The Julian date cannot be used in this case as the user suggested INTEGER as a method to obtain the date. This is a clear case where someone has placed a question without properly reseaching the results.
Jamie
March 6, 2008 at 8:51 am
Answers 1 and 3 were wrong for me, 2 was correct.
March 6, 2008 at 8:52 am
I voted for choice A because B doesn't account for leap years, and C is wrong.
There are two problems in the case statement. It requires both the day and month to be greater, then it subtracts a year for those who have already had their birthday.
The 1 and 0 should be reversed.
Further, if the month is before today's month, but the day is after it (or vice versa), the AND result will be FALSE, causing the wrong action to be taken.
If today's date is 3/5/2008 and the date of birth is 2/11/1980, the function will return 27 rather than the correct answer of 28 since 2 < 3 but 11> 5.
The statement should be:
[font="Courier New"]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[/font]
This is yet another example of why date calculation is so tricky. I'm excited about the DATE and TIME datatypes in 2008, whenever it actually ships.
But boss, why must the urgent always take precedence over the important?
March 6, 2008 at 8:53 am
Here we go again... another case of piss poor editing. The 3rd answer is flat wrong because the 0 and 1 are reversed in the THEN and ELSE clauses. In addition the ">=" should be just ">" or it's wrong if the birthdate is today. The approach is the best but the answer is flat wrong in a large number of cases. The 2nd answer is the "best" in that it produces the right answer in most cases, subject to accumulation of leap days.
Regards,
Greg Young
March 6, 2008 at 8:55 am
for me 1 & 2 return the correct age as I have had a birthday this year. For a birthday later in March then only Option 2 gets it right, as I would have expected. Hmm.
March 6, 2008 at 9:00 am
The third query, the way it is written returns an incorrect age.
This is the correct query:
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
">=" has to be replaced with ">"
March 6, 2008 at 9:07 am
The second option is also incorrect. It's true that the second option doesn't handle leap years. I tried 03/06/1964 (today's date is 03/06/2008) and got the correct age, however put 03/07/1964 and got the incorrect age. The process wouldn't realize the birthday hasn't happened until 03/18/1964.
March 6, 2008 at 9:18 am
Although the second answer produces the correct age for most, if not all, people, the FLOOR function returns the same datatype as is used within the function which is DECIMAL.
This is not TINYINT as specified in the question. So the second answer is wrong too as it does not fulfil the entire criteria. 😀
March 6, 2008 at 9:29 am
Again, Second answer was the only one that returned the correct answer for my birthdate (4/5/1961). Also there is clearly something wrong with the QOD when 77% of the people that answer it get it WRONG. :w00t: I am sure that we have some pretty smart people here.:D
March 6, 2008 at 9:33 am
I like others ran the queries and then answered the question, which was which of the three was the best answer. #2 was the best, one and two were wrong and #2 being closer to correct was the best question offered.
I did what was requested, got the best answer for the problem as offered and now you tell me it was wrong when it was clearly right? Do you work for the IRS or something? :):):):):)
Have a great day!
MIles...
Not all gray hairs are Dinosaurs!
March 6, 2008 at 9:48 am
The missing #4 option is almost correct. The last comparison for day should be <= to account for birthdays falling on the day you run the query.
DATEPART(d, @BirthDate) <= DATEPART(d, GETDATE())
March 6, 2008 at 9:49 am
I have to agree with the mob with the burning torches, # 3 doesn't work.
Viewing 15 posts - 31 through 45 (of 189 total)
You must be logged in to reply to this topic. Login to reply