computed age column

Posted on 2014-04-17
Last Modified: 2014-04-18
I want a computed age column based on DOB.  What the best way to do this?  Is there a built in age function?
Question by:HLRosenberger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 33

Accepted Solution

Big Monty earned 250 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

Open in new window

LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40007135
>> What the best way to do this? <<

Here's my preferred method.

    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? <<

Author Closing Comment

ID: 40009258
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 69

Expert Comment

by:Scott Pletcher
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

Author Comment

ID: 40009435

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

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question