Calculate age - Most easiest way

  • cfox 61877 (1/23/2015)


    I find this works better than a scalar function...

    CREATE FUNCTION dbo.AgeInYears

    (

    @BirthDate DATETIME2,

    @AsOfDate DATETIME2

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -

    CASE WHEN DATEPART(DY, @AsOfDate) < DATEPART(DY, @BirthDate) THEN 1

    ELSE 0

    END AS Age

    );

    Then a simple CROSS APPLY in the query...

    SELECT I.Name, I.Birthdate, A.Age

    FROM Individual AS I

    CROSS APPLY AgeInYears(I.Birthdate, CURRENT_TIMESTAMP) AS A

    WHERE A.Age >= 18;

    seems to be a day out in leap years e.g. @BirthDate = '2001/04/01' , @AsOfDate = '2016/03/31' gives 15 and it should still be 14

  • Thanks for catching that, andrew_dale!

    Here's the fix...

    CREATE FUNCTION dbo.AgeInYears

    (

    @BirthDate DATETIME2,

    @AsOfDate DATETIME2

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -

    CASE WHEN MONTH(@AsOfDate) < MONTH(@BirthDate) THEN 1

    WHEN MONTH(@AsOfDate) = MONTH(@BirthDate) AND

    DAY(@AsOfDate) < DAY(@BirthDate) THEN 1

    ELSE 0

    END AS Age

    );

  • Wow, who knew age would generate so many responses.

  • Iwas Bornready (5/11/2015)


    Wow, who knew age would generate so many responses.

    It is a sensitive subject:-D

    😎

Viewing 4 posts - 16 through 18 (of 18 total)

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