Automatic Age Update in SqlServer

Question

i have employees data in my database, if i insert the employee data it will automatically calculate the age that i written in stored procedure, but if the date of birth of an employee may be tomorrow if i retrieve the employee details will it automatically show the updated one

Code:

Declare @age float 
set @age=(select DATEDIFF(YYYY,@DATE_OF_BIRTH,
GETDATE())) update Employee_details set AGE=@AGE where
BADGE_NO=@BADGE_NO

Do not keep the age in the table. Keep the date of birth instead. — Zohar Peled, Aug 09 '17 at 05:48
Read this: https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate. You can use the solution there as a computed column. — Zohar Peled, Aug 09 '17 at 05:58

Max Szczurek · Accepted Answer · 2017-08-09T06:22:47.230

3

With SQL Server, if you store the date of birth, you can add the age as a computed column:

CREATE TABLE employees (id INT, name VARCHAR(25), dob DATE, 
    age AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000)
INSERT INTO employees (id, name, dob) VALUES (1, 'Max Smith', '12/31/1983')
INSERT INTO employees (id, name, dob) VALUES (1, 'Scott Smith', '2/8/1982')
INSERT INTO employees (id, name, dob) VALUES (1, 'Carolyn Smith', '11/1/1985')

Note the age column declaration - and thanks cars10m for providing this calculation! - which accurately gives you age based on a date-of-birth column and the current date:

age AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000)

select * from employees gives you (as of today, 8/9/2017):

id    name          dob         age
1     Max Smith     1983-12-31  33
1     Scott Smith   1982-02-08  35
1     Carolyn Smith 1985-11-01  31

Use a computed column!

edited Aug 09 '17 at 06:22

answered Aug 09 '17 at 06:00

Max Szczurek

4,324
2
20
32

2

This will run into problems with leap years. Here is an old solution that works reliably, even then: https://stackoverflow.com/a/1572411/2610061 . You should do something like; `select (convert(int,convert(char(8),getdate(),112))-convert(char(8),@dateOfBirth,112))/10000` – Carsten Massmann Aug 09 '17 at 06:06
Thanks @cars10m ! Incorporated your calculation into the answer. – Max Szczurek Aug 09 '17 at 06:25
Thanks A lot Programmers, I'm Just a Beginner now learning all functions in sql. – Mohamed jamal batcha Aug 09 '17 at 06:32

Automatic Age Update in SqlServer

Code:

1 Answers1