Post reply

Age Calculation not working

  • March 15, 2010 at 9:51 am

    #221737

    Hello all,

    Trying to get an accurate age calculation using T-SQL. Found a bunch on the net, most of which don't work properly when I really tested them out. The ones that did work correctly involved writing a function with case statements and such. Seems like a simple DATEDIFF should do it, but for whatever reason it's not. Take my sample:

    declare @BirthDate datetime

    declare @TargetDate datetime

    set @BirthDate = '3/15/1987'

    set @TargetDate = getdate()

    SELECT FLOOR(DATEDIFF(DAY, (@BirthDate), @TargetDate) / 365.25)

    With @BirthDate set as '3/15/1987' it returns 23 which is correct. However, if I use say '3/15/1977' it returns 32 which is incorrect. Does anyone have an accurate age calculation? I'm trying to do it with one line as this has to be used in a select statement.

    Note: I do not have the luxary of writing a stored procedure or function to accomplish this. I have to do it in a select statement.

    Thanks,

    Strick

  • March 15, 2010 at 9:55 am

    #1133844

    Check out this article[/url]. You may also want to read the discussion that followed.

  • March 15, 2010 at 9:58 am

    #1133849

    Try this

    SELECT DATEDIFF(Year,(@BirthDate,@TargetDate) - CASE WHEN DATEADD(Year, DATEDIFF (Year, (@BirthDate, @TargetDate), (@BirthDate) > @TargetDate THEN 1 ELSE 0 END

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • March 15, 2010 at 10:15 am

    #1133860

    Mark-101232 (3/15/2010)

    Try this

    SELECT DATEDIFF(Year,(@BirthDate,@TargetDate) - CASE WHEN DATEADD(Year, DATEDIFF (Year, (@BirthDate, @TargetDate), (@BirthDate) > @TargetDate THEN 1 ELSE 0 END

    Yes, this will work.

    Different subject, like your tag line.

  • March 15, 2010 at 10:55 am

    #1133889

    Lynn Pettis (3/15/2010)

    Mark-101232 (3/15/2010)

    Try this

    SELECT DATEDIFF(Year,(@BirthDate,@TargetDate) - CASE WHEN DATEADD(Year, DATEDIFF (Year, (@BirthDate, @TargetDate), (@BirthDate) > @TargetDate THEN 1 ELSE 0 END

    Yes, this will work.

    Different subject, like your tag line.

    Thanks!

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • March 15, 2010 at 1:23 pm

    #1134025

    Thanks all,

    This is a big help!

    Strick

Viewing 6 posts - 1 through 6 (of 6 total)

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