Best Way to Calculate Age

Question

Best Way to Calculate Age

Viewing 15 posts - 61 through 75 (of 189 total)

You must be logged in to reply to this topic. Login to reply

John R. Hanson SSC-Addicted Points: 442 More actions · Answer 1

I believe that 2 also is the most correct, and I believe that the Logical "AND" of month and day is the problem. If the Day portion is greater than today's date's Day, then it will evaluate incorrectly. Both 11/4/2000 and 11/11/2000 should evaluate to 7 years, but 11/11/2000 evaluates to 8 because the day is greater than today's date (the 6th). I too, ask for my points.

Cheers,

John R. Hanson

rs80 SSCrazy Points: 2312 More actions · Answer 2

2nd option seems to be incorrect as well. My birthday is 3/7, but 2nd option returned 28.

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 3

John Esraelo-498130

SSCertifiable

Points: 5914

March 6, 2008 at 11:44 am

#786259

funny

:w00t:

:rolleyes:

Cheers,
John Esraelo

Ellen Elliott Right there with Babe Points: 728 More actions · Answer 4

I thought #3 would be the correct answer, but

I checked all 3 against a date that was earlier in the year than today, later in the year than today and the same as today but with a different year.

The only one that was correct in all three cases was #2.

Øystein Fallo SSCommitted Points: 1845 More actions · Answer 5

Or, you can simply calculate age in months, and divide by 12...

DECLARE @DateOfBirth datetime;
DECLARE @Today datetime;
SET @DateOfBirth = '19960302';
SET @Today='20080301';
SELECT (DATEDIFF(mm, @DateOfBirth, @Today) - CASE WHEN DATEPART(d, @DateOfBirth)>DATEPART(d, @Today) THEN 1 ELSE 0 END) / 12

Duncan A. McRae SSC Veteran Points: 240 More actions · Answer 6

The case statement needs to be fixed. Where are your unit tests?! 😛

Lisa Phillip SSCrazy Points: 2991 More actions · Answer 7

The 1st and 3rd answers were both wrong for my birthday. The 2nd was the only correct option.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 8

I tried a few combinations, only the second one returned the correct number in years maybe not as a tiny integer, but correct.

Additionally, everything that requires a variable to be entered more the twice should be re-written as a function or SP.

The best way would be to call a person and ask "how old are you? "

Regards,Yelena Varsha

Ralph Schwehr SSC Enthusiast Points: 147 More actions · Answer 9

I too ran all three queries and only the first and second returned the correct value. I think you have to redo this question with correct possible answers 🙂

mpadierna1 Valued Member Points: 71 More actions · Answer 10

The last part of the third query must be THEN 1 ELSE 0 in order to work properly:

declare @DateOfBirth datetime

set @DateOfBirth = '31/07/1960'

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 1 ELSE 0 END

With this change, the output reads: 48, 47 and 47, otherwise it returns 48, 47, 48.

I want my two points back!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 11

About the second answer:

FLOOR function according to BOL return a data type of the input expression.

But try this and compare data length of the input and output of the FLOOR function for different types.

P.S. - I know that BOL says bit is not supported by FLOOR, it still works.

declare @i bit

select @i = 1