0

update the age column by D.O.B column automatically. whenever the sql server browse the column should be able to update itself. so i think it should be written when creating the table or alter table. what about using "Timestamp"? . how can i overcome?

Name        dob        age 

aaa     12-JUN-1985

bbb     15-MAY-1991

ccc     23-AUG-2000

the table should be

Name        dob        age

aaa     12-JUN-1985    28 

bbb     15-MAY-1991    22

ccc     23-AUG-2000    13
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
Razna
  • 1
  • 2
  • 1
    Don't store the age, there's no need. Do you have an application or other code that will use this information? If so, your app code can work it out. Otherwise, if it's just used in SQL for reporting, create a view that calculates the age. There's no need to store it. – Tanner Sep 01 '14 at 08:39

1 Answers1

5

You could create a computed column that calculates the age:

ALTER TABLE YourTable
ADD Age AS CAST(DATEDIFF(YEAR, dob, GETDATE()) AS TINYINT)

Please note that this basic age calculation is not precise, it will render incorrect results part of the year. A good calculation is a bit more complex and have to take date into account (and possibly leap year), and I'll leave that to you, or you could look at this: How to calculate age (in years) based on Date of Birth and getDate()

Community
  • 1
  • 1
jpw
  • 44,361
  • 6
  • 66
  • 86