I've got a database which consists of 2 fields known as
Age, for storing customers DOB and age correspondingly. I would like the
Age column to become instantly incremented by 1 each year, based on the DOB matching server date.
What is the easiest way for accomplishing this? I'm using asp.internet and sql server 2008.
Instead of store both
Age, produce a computed column up for grabs that computes 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 wish to persist the calculated value add the
One possibility, if you would like Age displayed in a long time and several weeks:
[AgeInDays] AS datediff(day, DateOfBirth, getdate())
then produce a view over your table that formats
AgeInDays into many several weeks.
Here's another possibility, utilizing a calculated column of
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 can examine for boundary cases...]