Datediff - establish age between two columns

I have the following SQL statement.


Bascally, I want to take the Age between the BirthDate column and DOR column and input the value into a third column called AgeReg.

How can I do this in SQL
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
dale has it right
al4629740Author Commented:
I just need to do this once for a specific set of records.  I don't want to make it a computed column
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Dale BurrellDirectorCommented:
Well just add the SQL you've shown to the query to wish to use it on?

You seem to already know what to do, or else I'm misunderstanding the question.
   DOR = date of registration
  [BirthDate] and [DOR] are of date/smalldatetime/datetime "data types"

Using a simple datediff(year,BIRTHDATE ,DOR) will not produce a reliable age at registration I'm afraid. Look at these results:
    |    abc | 1975-01-17 | 2013-10-02 |     38 |                38 |
    |    def | 1975-12-31 | 2013-10-02 |     37 |                38 |
    |    ghd | 1996-02-29 | 2013-10-02 |     17 |                17 |
    |    ijk | 1996-02-29 | 2013-01-02 |     16 |                17 |

Open in new window

The issue is that if the DOR day/month is earlier than the BIRTHDATE's day/month then the age will be incorrect by one year.

The above results were produced by this query:
          , convert(varchar(10) ,BirthDate, 121) AS BirthDate
          , convert(varchar(10) ,DOR, 121)       AS DOR
          , YEAR(DOR) - YEAR(BirthDate)
            - ( CASE
                   WHEN MONTH(BirthDate) > MONTH(DOR)
                     OR ( MONTH(BirthDate) = MONTH(DOR)
                        AND DAY(BirthDate) > DAY(DOR)
                   THEN 1
                   ELSE 0
                 END ) AS AgeReg
          , DATEDIFF(YEAR, BirthDate, DOR)  AS unreliable_AgeReg
    FROM Users

-- sample data used:
    	( id int identity primary key, [UserID] varchar(12), [BirthDate] datetime, [DOR] datetime)
    	([UserID], [BirthDate], [DOR])
    	('abc', '1975-01-17 00:00:00', '2013-10-02 00:00:00'),
    	('def', '1975-12-31 00:00:00', '2013-10-02 00:00:00'),
    	('ghd', '1996-02-29 00:00:00', '2013-10-02 00:00:00'),
    	('ijk', '1996-02-29 00:00:00', '2013-01-02 00:00:00')

Open in new window

Regarding the computed column, it would make sense if you can implement it as that calculation could be done once (as both dates don't change except for a mistake). However as you can see above it can be simply included into a query like any other calculation would be.

nb: IF (and I hope it's not true) either of the fields used in the calculation are a string type then the calculation would need amendment.
Anthony PerkinsCommented:
For a little history behind this question, see the question the author posed a couple of days ago:
add computed column from birthdate and time of registration column

What is strange is that Scott provided a valid answer, it was accepted and yet now the author wants to use the following which we all know is not the way to calculate age:

In case they still think that this is valid I would encourage them to use the following much repeated example:
SELECT DATEDIFF(YEAR, '20121231', '20130101')

Clearly the output here cannot be used as an age.
al4629740Author Commented:
The question is not the same as the difference I am looking for this time is using an update statement rather than a computed column.

Thanks for your effort
Anthony PerkinsCommented:
The question is not the same
I did not say it was.  I merely pointed out that your solution to calculate the age is flawed and it is surprising as Scott had already given you the correct solution, regardless of whether you use a computed column or not.
If you wanted an update statement I'd suggest asking for exactly that :)

But in addition, if you have been provided a correct algorithm, that should also have been part of the question too. We went down a rabbit hole to correct the algorithm I'm afraid.

Scott's accepted answer on Q_28254154 is neat, and applying that to an update statement would appear like the following (assuming a table called [Users]):
--optional, may already be done, note tinyint caters for 0 to 255
add [AgeReg] tinyint

-- sample update statement
SET AgeReg = DATEDIFF(YEAR, BirthDate, DOR) -
              CASE WHEN CONVERT(char(5), BirthDate, 101) > CONVERT(char(5), DOR, 101) 
              THEN 1 ELSE 0 END

-- optional
       BirthDate, DOR, AgeReg
FROM Users

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
Gentlemen....Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.