Best Way to Calculate Age

  • Comments posted to this topic are about the item Best Way to Calculate Age

  • If a person's DOB = '1988-02-24 15:00:00.000', the age should be 20 already as of today (March 5th, 2008).

    But the 3rd query returns 19.

    select DATEDIFF(yy, '1988-02-24 15:00:00.000', GETDATE()) - CASE WHEN DATEPART(m, '1988-02-24 15:00:00.000') >= DATEPART(m, GETDATE()) AND DATEPART(d, '1988-02-24 15:00:00.000') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

    Does the 3rd query return the right age?

  • I ran all 3 queries with my DOB and the first and 3rd ones returned the incorrect value.

    The 2nd query had the correct value.

    looking at the case statement I think you have the 1 and 0 around the wrong way

  • I also ran all 3 queries for my DOB and 2nd was correct, both 1st and 3rd were wrong.

  • The first and second answers were correct for me. The third answer was definitely wrong in my case. :hehe:

  • Jumping on the bandwagon here: only the second statement returned the correct value for my birthdate. Gimme my point! ARGH!

    😛

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • Howdy, thanks for the interesting question.

    The 3rd answer was wrong for my DOB. It showed a younger value though, which is appreciated.

  • Definitely the second answer!

  • In my case, like many others, the second answer was the only correct one. My birthday is not until September.

  • The third solution returns "51" for DOB 08/21/1957.

    select DATEDIFF(yy, '08/21/1957', GETDATE()) - CASE WHEN DATEPART(mm, '08/21/1957') >= DATEPART(mm, GETDATE()) AND DATEPART(d, '08/21/1957') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

    Clearly, this is INCORRECT.

  • Only the second query is getting the right answer. #1 and #3 are getting +1 to the my current age.

  • The third solution is clearly incorrect - given a datetime value of 01/26/1978.

    i.e.

    declare @DateOfBirth datetime

    set @DateOfBirth = '1978-01-26 00:00:00.000'

    select @DateOfBirth

    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

    Returns:

    30 - Correct

    30 - Correct

    29 - Incorrect

  • I intuitively choose 2 because 1 and 3 are definitely wrong. 1 doesn't take care of month and day of birth at all and 3 does it in the wrong way. But the are all wrong. It can be easily verified with the script below. Play around with some values at the beginning and end of the month in both dates. I added a fourth solution which I think is correct.

    declare @DateOfBirth datetime

    declare @Today datetime

    select @DateOfBirth = '2004/03/01'

    select @Today = '2008/02/29'

    select

    DATEDIFF(yy, @DateOfBirth, @today)

    select

    FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, @Today)) / 365.0)

    select

    DATEDIFF(yy, @DateOfBirth, @today)

    - CASE

    WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, @Today) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, @Today) THEN

    0

    ELSE

    1

    END

    select

    DATEDIFF(yy, @DateOfBirth, @today)

    - CASE

    WHEN DATEPART(m, @Today) > DATEPART(m, @DateOfBirth)

    OR (DATEPART(m, @Today) = DATEPART(m, @DateOfBirth)

    AND DATEPART(d, @Today) >= DATEPART(d, @DateOfBirth)) THEN

    0

    ELSE

    1

    END

  • Of course, I suppose the real point of the question was to draw attention to the fact that (1) was wrong and (2) was inaccurate. But it would have been helpful to have a correct solution in the mix. 🙂

    Derek

  • I originally thought the 1 and 0 were switched but actually, the two comparisons should be >=

Viewing 15 posts - 1 through 15 (of 189 total)

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