Search This Blog

Determine the age from DOB

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

No comments: