Solved

# computed age column

Posted on 2014-04-17
Medium Priority
460 Views
I want a computed age column based on DOB.  What the best way to do this?  Is there a built in age function?
0
Question by:HLRosenberger
• 3
• 2

LVL 34

Accepted Solution

Big Monty earned 1000 total points
ID: 40007048
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
``````
0

LVL 70

Assisted Solution

Scott Pletcher earned 1000 total points
ID: 40007135
>> 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

LVL 1

Author Closing Comment

ID: 40009258
thanks!
0

LVL 70

Expert Comment

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

DECLARE @dob  datetime

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

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

LVL 1

Author Comment

ID: 40009435
ScottPletcher

I do not follow.  I'm working with both solutions trying to get he correct age.
0

LVL 70

Expert Comment

ID: 40009584
OK, good luck.  I can't imagine that an hours-based method could ever be fully accurate.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.