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 )
DECLARE @vAge INT
IF @pDateOfBirth >= @pAsOfDate
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
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
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 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.
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.