Technical Article

Scalar Function to Determine Age at a Given Date

,

This function was written to supplant cumbersome age-at calculations.  To execute, simply set a local INT variable equal to the output of the function.  For example, the following, given my birthdate, computes my own age:

    DECLARE @age_at INT

    SET @age_at = dbo.f_age_at ('2/16/1954', GETDATE ())

CREATE FUNCTION f_age_at
                  (@f_birthdate   DATETIME
                 , @f_age_at_date DATETIME)
RETURNS INT
AS
/*
|| =====================================================================
||
|| Purpose:  calculate an age, in integers, given a birthdate and any
||           given date.
||
|| Inputs:   @f_birthdate
||           @f_age_at_date
||
|| Outputs:  @retc, contains the age
||             or a large negative number signifying an error condition
|| =====================================================================
*//*
|| =====================================================================
|| Local variable declarations...
|| =====================================================================
*/BEGIN
  DECLARE @age_at INT

/*
|| =====================================================================
|| Main logic...
|| =====================================================================
*/  SELECT @age_at = -9999999

  IF @f_birthdate <= @f_age_at_date
--THEN
    BEGIN
      SELECT @age_at = DATEDIFF (YY, @f_birthdate, @f_age_at_date)

      IF ((MONTH (@f_age_at_date) * 100) + DAY (@f_age_at_date))
       < ((MONTH (@f_birthdate)   * 100) + DAY (@f_birthdate))
    --THEN
        SELECT @age_at = @age_at - 1
    --END IF
    END
--END IF
  
  RETURN @age_at
END

Rate

Share

Share

Rate