Post reply

Best Way to Calculate Age

  • March 6, 2008 at 11:35 am

    #786253

    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

  • March 6, 2008 at 11:37 am

    #786255

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

  • March 6, 2008 at 11:44 am

    #786259

    funny

    :w00t:

    :rolleyes:

    Cheers,
    John Esraelo

  • March 6, 2008 at 12:18 pm

    #786278

    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.

  • March 6, 2008 at 12:22 pm

    #786280

    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

  • March 6, 2008 at 12:32 pm

    #786292

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

  • March 6, 2008 at 12:48 pm

    #786303

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

  • March 6, 2008 at 1:31 pm

    #786334

    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

  • March 6, 2008 at 1:49 pm

    #786341

    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 🙂

  • March 6, 2008 at 2:52 pm

    #786372

    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!

  • March 6, 2008 at 3:44 pm

    #786391

    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

    select datalength(@i),datalength(floor(@i))

    go

    declare @i tinyint

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    declare @i smallint

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    declare @i int

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    declare @i numeric

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    declare @i real

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    declare @i float

    select @i = 1

    select datalength(@i),datalength(floor(@i))

    go

    Regards,Yelena Varsha

  • March 6, 2008 at 4:15 pm

    #786401

    # 2 is the "most" correct but it needs to be changed to

    FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @dob, @today)) / 365.24219)

    to maximize the "correctness"

    I use

    SELECT DATEDIFF(year, @dob, getdate()) -

    CASE WHEN DATEADD(year, DATEDIFF(year, @dob, getdate()), @dob) > @today THEN 1 ELSE 0 END AS Age

    where @dob = date of birth to calculate age

    but its important to note it makes a differnce when a leap year birthday actually (or legally) occurs in non-leap year - Feb 28 or March 1st.

  • March 6, 2008 at 4:23 pm

    #786408

    but its important to note it makes a differnce when a leap year birthday actually (or legally) occurs in non-leap year - Feb 28 or March 1st.

    So, when does a leap year birthday legally occur in a non-leap year?

    😎

  • March 6, 2008 at 4:48 pm

    #786413

    The Third answer should be correct only the CASE condition values are reversed.

    Like this

    "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 "

  • March 6, 2008 at 4:55 pm

    #786415

    Lynn Pettis (3/6/2008)

    but its important to note it makes a differnce when a leap year birthday actually (or legally) occurs in non-leap year - Feb 28 or March 1st.

    So, when does a leap year birthday legally occur in a non-leap year?

    😎

    it doesnt 😉

    Legally I believe it varies. In England and Wales I believe it is on Feb 28 but in other locations (probably due to lacking/faulty computer systems) it occurs on March 1st.

    For example, I believe the Michigan Vehicle Code assigns a birthday of March 1st to everyone born on Feb 29. This makes the calculation of expiration date easy but unfortunately leapers have to wait an additional day before they turn 21.

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

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