computed age column

I want a computed age column based on DOB.  What the best way to do this?  Is there a built in age function?
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
no, but you can do something like this:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
    ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
    ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> What the best way to do this? <<

Here's my preferred method.

SELECT
    DATEDIFF(YEAR, dob, GETDATE()) - CASE WHEN CONVERT(char(5), dob, 1) > CONVERT(char(5), GETDATE(), 1) THEN 1 ELSE 0 END as age

Leap year birthdays are the tricky part.  The code above treats Mar 1 as the birthday for someone born on Feb 29.  Code will need adjusted if you need to consider Feb 28 their birthday in non-leap years (believe it or not, some govts actually have laws on how this needs calculated, esp. for retirement, years-of-service, etc.).



>>  Is there a built-in age function? <<
No.
0
 
HLRosenbergerAuthor Commented:
thanks!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Scott PletcherSenior DBACommented:
Interesting choice.  Exactly one year (or nn years) after my birthday, shouldn't I be one year (or nn years) old?!


DECLARE @dob  datetime
SET @dob=DATEADD(YEAR, -1, GETDATE())

SELECT @dob AS date_of_birth, GETDATE() AS todays_date,
    DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc


SET @dob=DATEADD(YEAR, -10, GETDATE())

SELECT @dob AS date_of_birth, GETDATE() AS todays_date,
    DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc
0
 
HLRosenbergerAuthor Commented:
ScottPletcher

I do not follow.  I'm working with both solutions trying to get he correct age.
0
 
Scott PletcherSenior DBACommented:
OK, good luck.  I can't imagine that an hours-based method could ever be fully accurate.
0
All Courses

From novice to tech pro — start learning today.