how to update database field every year

Question

I have a database which contains 2 fields called DateOfBirth and Age, for storing users DOB and age respectively. I want the Age column to be automatically incremented by 1 every year, according to the DOB matching server date.

What could be the best way for achieving this? I am using asp.net and sql server 2008.

Answer 1

Rather than store both the DateOfBirth and Age, create a computed column on the table that calculates the age:

[Age] AS datediff(year,  DateOfBirth, getdate()) 

So in yout table creation:

-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
    [id] [int] not NULL,
    [DateOfBirth] [datetime] NULL,
    -- etc...
    [Age] AS datediff(year,  DateOfBirth, getdate()) 
)
GO 

If you want to persist the computed value add the PERSISTED keyword.

One possibility, if you want Age displayed in years and months:

    [AgeInDays] AS datediff(day,  DateOfBirth, getdate()) 

then create a view over your table that formats AgeInDays into years and months.

Here is another possibility, using a computed column of [AgeYears]:

create view vwCCtestAge
AS
select 
   id, 
   dateofbirth,
   cast([AgeYears] as varchar(4)) + ' years ' + 
      cast(datediff(month, DateOfBirth, getdate()) 
           - case when (AgeYears > 0) then (AgeYears - 1)*12 
                  else 0 
             end as varchar(4)) + ' months' as Age
   from cctest2
   GO

[You should check for boundary cases...]