SQL Server - Add a column to a table that calculates the age

Question

I would like to implement a query to add an AGE column to my table that would calculate the age of each record using the date column (mcsDate) that exists already.

Could anyone help me with this?

Thanks!

http://stackoverflow.com/questions/2533890/how-to-get-an-age-from-a-d-o-b-field-in-mysql or http://stackoverflow.com/questions/5773405/calculate-age-in-mysql-innodb — Adrian Lynch, Feb 25 '16 at 15:20
What flavor of sql is this? Some implementations allow for so-called 'computed' columns that are dependent on formulas which may use other fields in the row within them. — SWalters, Feb 25 '16 at 15:22
Yes I am aware the age would change each year and that's one of the things I was looking to do. — Garrick Brim, Feb 25 '16 at 15:24
That's not a very good idea. Any day somebody may turn 1 year older - and you don't want to run an UPDATE every day to make sure the ages are up to date. Don't store computed values (depending on other columns.) Create a view instead, that returns the table columns plus AGE column! — jarlh, Feb 25 '16 at 15:26
Now I see you're using SQL Server, then a computed column will do fine. (See JaydipJ's answer.) — jarlh, Feb 25 '16 at 15:27
SQL Server then: http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate - Either way, this question has been answered. — Adrian Lynch, Feb 25 '16 at 15:32

Jaydip Jadhav · Accepted Answer · 2016-02-25T15:30:43.943

1

Create Computed Column

CREATE TABLE [dbo].[DateDemo]
(
    [MDate] [date] NULL,
    [age]  AS (datediff(day, [MDATE], getdate())) -- This is a computed column
);

edited Feb 25 '16 at 15:30

answered Feb 25 '16 at 15:23

Jaydip Jadhav

12,179
6
24
40

Thanks, is there a way to have that age in the number of days not years? I'm apologize I should have mention that – Garrick Brim Feb 25 '16 at 15:27
@GarrickBrim: Have a look at [DATEDIFF Function](http://www.techonthenet.com/sql_server/functions/datediff.php) – huMpty duMpty Feb 25 '16 at 15:29
@GarrickBrim Yes just change `YEAR` to `DAY` . Updated Ans – Jaydip Jadhav Feb 25 '16 at 15:30
Check datediff reference. Datepart (first) parameter can have many values according to your need. https://msdn.microsoft.com/en-us/library/ms189794(v=sql.110).aspx – Chinthaka Senaratne Feb 25 '16 at 15:32
Just be aware that `DATEDIFF` counts *transitions* rather than what a human being might count. So `DATEDIFF(year,'20151231','20160101')` returns 1 even though only a single day has passed, and similarly `DATEDIFF(day,'2015-12-31T23:59:59','2016-01-01T00:00:01')` returns 1 even though only 2 seconds have elapsed. If you're happy to accept that, all is well. Otherwise, you need more complex expressions. – Damien_The_Unbeliever Feb 25 '16 at 15:40
@Damien_The_Unbeliever Yes Right. Thanks for Info :) – Jaydip Jadhav Feb 25 '16 at 15:50
@Damien_The_Unbeliever I will keep that in mind for the future. For this particular measure it's not as important. – Garrick Brim Feb 25 '16 at 18:08

score 0 · Answer 2 · answered Feb 25 '16 at 15:55

If you want to calculate age in years the way humans do birthdates, you should calculate it the way humans calculate ages. It's really not difficult. It's the only way to ensure that you have no errors from leap years.

CREATE TABLE DateTable (
    BirthDate DATE,
    Age AS YEAR(getdate()) - YEAR(BirthDate) - CASE 
        WHEN MONTH(BirthDate) > MONTH(getdate())
            OR (
                MONTH(BirthDate) = MONTH(getdate())
                AND DAY(BirthDate) > DAY(getdate())
                )
            THEN 1
        ELSE 0
        END
    );

Be advised if you're programming for international programs, however, that some countries use East Asian age reckoning.

SQL Server - Add a column to a table that calculates the age

2 Answers2