ActiveHub.hub

Parameters

Name	Type	Mode
@BirthDate	datetime	IN
@AgeOnDate	datetime	IN

Definition

--/ 	 KitSection = Tables
-- 	 KitSection = UserFunctions
--KitManagerFileID=21438
--FileName=hub.udf_AgeInYearsMonths.sql
--SubmittedBy=Rae Andrews (ACTIVE\randrews)

--DROP FUNCTION hub.udf_AgeInYearsMonths;
CREATE   FUNCTION hub.udf_AgeInYearsMonths (
    @BirthDate DATETIME,
    @AgeOnDate DATETIME)
RETURNS FLOAT
WITH SCHEMABINDING  
AS
BEGIN
    DECLARE @AgeInYearsMonths FLOAT = NULL;

    IF @BirthDate IS NOT NULL AND  @BirthDate > '1899-12-30' BEGIN
        DECLARE @YearNow INT = DATEPART(YEAR, @AgeOnDate);
        DECLARE @MonthNow INT = DATEPART(MONTH, @AgeOnDate);
        DECLARE @DayNow INT = DATEPART(DAY, @AgeOnDate);
        DECLARE @BirthYear INT = DATEPART(YEAR, @BirthDate);
        DECLARE @BirthMonth INT = DATEPART(MONTH, @BirthDate);
        DECLARE @BirthDay INT = DATEPART(DAY, @BirthDate);
        DECLARE @YearAge INT = @YearNow - @BirthYear;
        DECLARE @MonthAge INT = @MonthNow - @BirthMonth;

        RETURN CAST(CONCAT(
            CAST(CASE
                WHEN @MonthNow < @BirthMonth THEN (@YearAge - 1)
                WHEN @MonthNow = @BirthMonth AND @DayNow < @BirthDay THEN (@YearAge - 1)
                ELSE @YearAge
                END AS VARCHAR(4))
            , '.',
            CAST(CASE
                WHEN @DayNow < @BirthDay AND (@MonthAge - 1) < 0 THEN ((@MonthAge - 1) + 12)
                WHEN @DayNow < @BirthDay THEN (@MonthAge - 1)
                WHEN @MonthAge < 0 THEN (@MonthAge + 12)
                ELSE @MonthAge
                END AS VARCHAR(2))
            ) AS FLOAT);
    END;

    RETURN @AgeInYearsMonths;
END;