Oct162007

Calculating Age In SQL

Goog Feed, General, SQL    Comments (3)


So, I was building an application at work that required me to know the age of an individual before further processing could be done on the record.

I thought this would be a piece of cake with the "dateDiff(yy,date1,date2)" function. That only gives the year difference. For instance if today's date is 10/16/2007 and you we're born on 10/17/1981, the dateDiff function would tell you that you're 26 years old when in fact you don't turn 26 till tomorrow.

This became problematic for me because I needed to know to the day how old this individual was at any given moment.

After a bit of browsing and small modifications on my end here is what I ended up using.

SELECT DATEDIFF(YY, '10/15/1981', getDate()) - CASE WHEN( (MONTH('10/15/1981')*100 + DAY('10/15/1981')) > (MONTH(getDate())*100 + DAY(getDate())) ) THEN 1 ELSE 0 END as Age

What the above SQL statement does is first grab the year difference. Then the CASE statement takes the month multiplied by 100 and adds the day of the date to that total. It also does the same thing for the second date passed in. It then evaluates the two and either leaves the year alone or subtracts one from it.

For this example I hard-coded the date of "10/15/1981" into the statement, but in my real-life example that became a sub-query based on an ID. Hope you find this as useful as I did.



Comments (3) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 2980 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
Here's another approach to consider: SELECT (DATEDIFF(dd, '10/15/1981', getDate()) / 365.25) AS Age
# Posted By Ken | 11/3/07 12:36 PM
@ Ken: I like that Ken, I tried that type of idea but couldn't figure a way to get over the leap year hump, it looks like your solution would do that.
# Posted By Joe Gautreau | 11/3/07 4:42 PM
I like your solution Joe, it work nicely. I tried Ken's but it retuns numbers after the decimal. I tried (DATEDIFF(dd, '10/15/1981', getDate()) / 365.25) AS Left(Age,2) but that just threw an error. Any idea how to implement Ken's solution to return ages 0-99?

Cheers
# Posted By James | 3/10/08 6:39 AM
[Add Comment]