ActiveHub
.hub
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
udf_AgeInYearsMonths
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;