Tip of the Day : How to Join with an Inline User-Defined Function
Get Age Function

Get Age Function

Storing the age of an individual in a Users or Employees table is not a good database design simply because this changes every year.  You don't want to update the database every day (or every week) just to update the age.  In place of the age, the birth date of the individual should be stored instead.

Given the birth date of the individual, the age can easily be computed.  The user-defined function below computes the age an individual for any given day given the birth date. 

CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth    DATETIME, 
                                     @pAsOfDate       DATETIME )
RETURNS INT
AS
BEGIN

    DECLARE @vAge         INT
    
    IF @pDateOfBirth >= @pAsOfDate
        RETURN 0

    SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

    IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
      (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
       DAY(@pDateOfBirth)   > DAY(@pAsOfDate))
        SET @vAge = @vAge - 1

    RETURN @vAge
END
GO

Description

The user-defined function above accepts two inputs, namely the date of birth and a reference date.  You may be wondering why have the second parameter since it would be better to just get the age as of the current date.  There are 2 reasons for this: first, to give the flexibility to the function to be able to determine the age of an individual at any reference date; and second, the function GETDATE(), or any non-deterministic function, is not allowed to be used inside user-defined functions.

The first part of the function is to check if the date of birth is later than the reference date.  If this is the case, the function simply returns 0 as the age.

The second part of the function computes for the age given the date of birth and the reference date.  It first gets the difference between the years of the two dates (SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)).  Then it subtracts one to this difference if the birth date, excluding the year, is later than reference date, also excluding the year.  This means that the birth date has not passed for this year.

You may ask, why do the second step when the first step should be enough.  Well, the DATEDIFF function with the YY parameter simply returns the difference between the years, without considering the date.  So if today is April 11, 2005, without checking for the date, any birth date in year 1990 will be age 15 even if the birth date has not yet passed for year 2005.

Usage

Here's an example on how to use this function. 

SELECT * FROM [dbo].[Users]
WHERE [dbo].[ufn_GetAge] ( [BirthDate], GETDATE() ) >= 18

This statement selects all users whose age is 18 years and above.