How to properly populate the age attribute?

Question

I have an MS-SQL Server which keeps track on a number of clients. If the birthday is known it is stored as a datetime attribute called dayOfBirth. Now I would like to have another attribute age, which keeps track of the current age of the client. Since age can change any day I figured a script might be the best idea.

First thing I did, was to create a stored procedure which computes the age given the birthday as datetime. Here is what I came up with:

USE [MyDB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE CalculateAge
@dayOfBirth datetime,
@age INT OUTPUT
AS

DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int

SET @today = GETDATE()
SET @thisYearOfBirth = DATEADD(year, @dayOfBirth, @today), @dayOfBirth)
SET @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
SET @age = @years

Afterwards I created another script which runs through all records that have a non null dayOfBirth attribute and updates the age filled accordingly.

USE [MyDB]
GO

DECLARE @age int;
DECLARE @birth datetime;
DECLARE @id intl
DECLARE cursorQuery CURSOR FOR SELECT clientId FROM Clients WHERE dayOfBirth IS NOT NULL;

OPEN cursorQuery

FETCH NEXT FROM cursorQuery INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @birth = (SELECT dayOfBirth from Kunden where clientId=@id);
    EXEC dbo.CalculateAge @birth, @age OUTPUT;
    UPDATE Clients SET Age = @age WHERE clientId = @id;
    FETCH NEXT FROM cursorQuery INTO @id
END

CLOSE cursorQuery
DEALLOCATE cursorQuery

I would trigger the script above once per day to populate the age attribute. Thats what I have so far, but I have the feeling there is plenty of room for improvement.

** Thanks Sung Meister **

I ended up with something like this:

CREATE TABLE Client (

ID int identity(1,1) primary key,
DOB datetime null,
AGE as (case
    when DOB is null then null
    else DATEDIFF(YY,DOB,GETDATE()) - CASE WHEN (MONTH(GETDATE()) = MONTH(DOB) AND DAY(DOB) > DAY(GETDATE()) OR MONTH(GETDATE()) > MONTH(DOB)) THEN 1 ELSE 0 END
    end
    )
)

Answer 1

You really shouldn't be storing the age within the database as it is easily calculated and changes on a daily basis.

I would suggest that you keep the date of birth field and just calculate the age as you need it. If you wish to have the age selected along with the other attributes then consider a view, perhaps with a user defined function to calculate the age.

The following is an example (untested) UDF that you could use

CREATE FUNCTION age 
(
    @userId int
)
RETURNS int
AS
BEGIN

    DECLARE @Result int

    SELECT @Result = DATEDIFF(year, dateofBirth, getDate())
    FROM person
    WHERE userId = @userId

    RETURN @Result

END
GO

Then within your queries you can do something similar to the following,

SELECT *,
       dbo.age(userId) as age
FROM   person

In answer to your question on sorting etc, then you could create a view on the data and use that to show the data so something like this (untested)

CREATE VIEW personview(firstname, surname, dateOfBirth,age) AS 

SELECT firstname,
       surname,
       dateOfbirth,
       dbo.age(userid)
FROM   person

You can then use this view to perform your queries, there could be a performance hit for filtering and sorting based on the age and if you regularly sort/filter based upon the age field then you may want to create an indexed view.

Answer 2

Use a view and/or function. Never store two fields that arew based on the exact same data if you can help it as they will eventually get out of sync.