2

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.

sawa
  • 165,429
  • 45
  • 277
  • 381
Abbas
  • 4,948
  • 31
  • 95
  • 161

1 Answers1

4

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...]

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • @Mitch: can you please elaborate, what you mean by computed column, and how it will help me for updating age field every year.. automatically... no manuals... – Abbas Mar 24 '11 at 05:05
  • is it possible to display whole age including month and days... in sql server – Abbas Mar 24 '11 at 05:15
  • @Mitch: can you please help me for the same also.. Thanks in advance – Abbas Mar 24 '11 at 05:18
  • @Abbas: I'm sure it would make a good *new* question to ask *after you've made sure there hasn't been one already*. Take a look, for example, at this one: http://stackoverflow.com/questions/57599/how-to-calculate-age-in-tsql-with-years-months-and-days – Andriy M Mar 24 '11 at 07:01
  • @Andriy M : it's a slightly different question – Mitch Wheat Mar 24 '11 at 07:02
  • @Mitch Wheat: In what way? I'm not quarrelling, just can't see the difference. – Andriy M Mar 24 '11 at 07:04
  • @Mitch: Well, I understand that this one is about updating, but the main question is probably about calculating, isn't it? – Andriy M Mar 24 '11 at 07:05