How to update Age column based on DateOfBirth column in a SQL Server query

Question

I need to write a SQL query to update Age column from a DateOfBirth column. Below is a sample table with some ready data.

CREATE TABLE Students
(
    ID INT IDENTITY,
    Name VARCHAR (100),
    DateOfBirth date,
    Age INT,
    ClassID INT,
    SectionID INT,
    PRIMARY KEY (ID)
)

INSERT INTO Students (Name, DateOfBirth, Age, ClassID, SectionID) 
VALUES ('Adam','2003-06-16', 0, 6, 2)

INSERT INTO Students (Name, DateOfBirth, Age, ClassID, SectionID) 
VALUES ('Botham','2001-03-22', 0, 8, 1)

INSERT INTO Students (Name, DateOfBirth, Age, ClassID, SectionID) 
VALUES ('Hillton',Null, 0, 7, 2)

INSERT INTO Students (Name, DateOfBirth, Age, ClassID, SectionID) 
VALUES ('Rasty','2004-12-02', 0, 5, 1)

INSERT INTO Students (Name, DateOfBirth, Age, ClassID, SectionID) 
VALUES ('Holistar',Null, 0, 8, 1)

If you already have a DateOfBirth column, why do you need to also keep an Age column? — Zohar Peled, Oct 13 '15 at 07:52
You could have it as a computed column if you really need it as a separate column — Rowland Shaw, Oct 13 '15 at 07:54
Agree with Zohar - in general, don't *store* data that you can compute (unless the computation is expensive, and even there, look to tools built into the database system so that you don't have to perform the calculation manually) — Damien_The_Unbeliever, Oct 13 '15 at 07:54
Please think to restructure your table or function to calculate age based on DoB column. — Paresh J, Oct 13 '15 at 07:56
@Zohar Peled did u give a negative vote? why? Its an existing system and somehow Age wasnt calculated perfectly. So we need to update the Age column. Did u get me? — LazyLoading, Oct 13 '15 at 07:58
@CallumLinington - triggers fire once based on a specific DML operation. They're not suitable for maintaining data that needs to change in response to external realities (such as the date changing) — Damien_The_Unbeliever, Oct 13 '15 at 07:59
@aiolin: i didn't downvote but i think that others downvoted because you haven't shown any effort. — Tim Schmelter, Oct 13 '15 at 08:03
@aiolin it is very bad form to accuse other users of downvoting and likely only to lead to pointlessly uncomfortable situations. Just accept it and move on. Or - here's a revolutionary thought - you could even try to make your question better, so that your unknown anonymous downvoter might reverse their decision or even upvote you. Also, it wasn't me. — underscore_d, Oct 13 '15 at 08:22

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

Instead of having a column, that needs to be constantly updated. I'd go for computed column and I'd use formula from this question: How to calculate age (in years) based on Date of Birth and getDate()

CREATE TABLE Students
(
    ID INT IDENTITY
    , Name VARCHAR(100)
    , DateOfBirth DATE
    , Age AS CONVERT(INT, ROUND(DATEDIFF(HOUR, DateOfBirth, GETDATE()) / 8766.0, 0))
    , ClassID INT
    , SectionID INT
    , PRIMARY KEY (ID)
);

Having this, your Age column should always be up-to-date.

edited May 23 '17 at 10:27

Community

1
1

answered Oct 13 '15 at 08:14

Evaldas Buinauskas

13,739
11
55
107

The age "formula" isn't accurate. You are assuming 365, 25 days per year. – CFreitas Oct 13 '15 at 08:20
I referenced a SO question for the formula. My initial suggestion is to use computed column. That's it. – Evaldas Buinauskas Oct 13 '15 at 08:23
Cribbing someone else's inadvisable answer isn't a very good excuse. – underscore_d Oct 13 '15 at 08:29
Why do you think it's a crib? – Evaldas Buinauskas Oct 13 '15 at 08:33
Bad choice of word maybe, but you admitted you nearly copied it from the other answer. It doesn't seem a good idea to paste an answer from somewhere else without much scepticism - especially when that answer already has highly upvoted comments pointing out what's wrong with it. – underscore_d Oct 13 '15 at 08:36
I referenced where I took formula from instead of telling that's my own. Is that a copy? I don't think so. On top of that, my answer is focused on having computed column and how to design table in a different way, than OP asked. [How to reference material written by others](http://stackoverflow.com/help/referencing). Think I followed rules. – Evaldas Buinauskas Oct 13 '15 at 08:52

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

i hope this will help you to find your solution

SELECT Name,Age = DATEDIFF(day,DateOfBirth,GETDATE()),
[YEARS]  = DATEDIFF(day,DateOfBirth,GETDATE()) / 365,
[MONTHS] = (DATEDIFF(day,DateOfBirth,GETDATE()) % 365) / 30,
[DAYS]   = (DATEDIFF(day,DateOfBirth,GETDATE()) % 365) % 30
FROM Students

Your Age column is INTEGER so you can use it which will give you Age in Year

UPDATE Students SET Age = DATEDIFF(day,DateOfBirth,GETDATE()) / 365

if you get more accurate answer then you can use

UPDATE Students SET Age = FLOOR(DATEDIFF(day, DateOfBirth, GETDATE()) / 365.242)

this Reference will help you to calculate leap year

edited May 23 '17 at 12:22

Community

1
1

answered Oct 13 '15 at 08:02

wiretext

3,302
14
19

4

Because we all know that every year contains exactly 365 days? – Damien_The_Unbeliever Oct 13 '15 at 08:06
1

humble request to **down voters** please leave comment so i can figure out the problem and try to solve – wiretext Oct 13 '15 at 08:11
2

Well, as I alluded to - for every approximately four years of age, this calculation becomes inaccurate by a day, because of leap days. – Damien_The_Unbeliever Oct 13 '15 at 08:15
@aiolin, please un-accept this. It is chronologically wrong and only going to make your seemingly legacy system worse than it already is. You seem to have put even less effort into accepting this than you did into writing your question. – underscore_d Oct 13 '15 at 08:16
@Damien_The_Unbeliever and every month contains 30 days, yup – underscore_d Oct 13 '15 at 08:17
@Damien_The_Unbeliever should i delete this OR put comment without leap year?? – wiretext Oct 13 '15 at 08:23
Given that it's an accepted answer that you now know is poor advice, I think the best way is be to edit it to be correct - or if you can't, delete it. but I'm not a moderator, etc. Half of the problem here is that the OP has accepted it without any scepticism and might never realise that it's inaccurate. – underscore_d Oct 13 '15 at 08:33

CFreitas · Answer 3 · 2015-10-13T08:15:29.330

0

Although I don't agree with your table, want you want is:

update Students
set Age = DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN (MONTH(DateOfBirth) > MONTH(GETDATE())) OR (MONTH(DateOfBirth) = MONTH(GETDATE()) AND DAY(DateOfBirth) > DAY(GETDATE())) THEN 1 ELSE 0 END

edited Oct 13 '15 at 08:15

answered Oct 13 '15 at 08:03

CFreitas

1,647
20
29

1

Unfortunately, `DATEDIFF` measures *transitions*. `DATEDIFF(year,'20131231','20140101')` returns 1, even though only one day has elapsed. – Damien_The_Unbeliever Oct 13 '15 at 08:07
@Damien_The_Unbeliever: You're right. My bad. It's fixed. I would still go with the computed column. – CFreitas Oct 13 '15 at 08:16

How to update Age column based on DateOfBirth column in a SQL Server query

3 Answers3