Home Forums SQL Server 2005 T-SQL (SS2K5) Calculating age RE: Calculating age

  • September 7, 2013 at 4:03 pm

    #1648361

    hariharannkl (9/7/2013)

    It is very simple, just use the following sql statement,

    (DATEDIFF(YY,DOB,GETDATE()) - CASE

    WHEN MONTH(DOB)<MONTH(GETDATE()) AND (MONTH(DOB)=MONTH(GETDATE()) OR DAY(DOB)>DAY(GETDATE())) THEN 0 ELSE 1

    END)

    Here DOB is Columname, and GETDATE() is a function which gives the current date.

    It would appear that your's also has a Leap Year problem but in the opposite direction. Your's returns "0" for the following dates which is incorrect.

    DECLARE @DOB DATETIME

    ,@Now DATETIME

    SELECT @DOB = '2000-03-31'

    ,@Now = '2001-03-31'

    SELECT DATEDIFF(YY,@DOB,@Now) - CASE

    WHEN MONTH(@DOB)<MONTH(@Now) AND (MONTH(@DOB)=MONTH(@Now) OR DAY(@DOB)>DAY(@Now)) THEN 0 ELSE 1

    END

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)