Best Way to Calculate Age

  • 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

  • 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!

  • 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)

    😎

  • 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

  • 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.

  • 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

  • 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!!

  • 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

    😎

  • 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.

  • 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.

  • 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

  • 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

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Agree with the fourth statement, it's the only one that returns a correct value.

  • /*

    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