How Old Are You?

Posted At : December 14, 2007 1:58 PM | Posted By : Gary Gilbert
Related Categories: misc

A relatively simple question but with a possibly painful response depending on who you ask and how you ask.

Men typically aren't all that concerned about answering the question and generally if it's close to a birthday will say almost xx (or in my case almost 37).

If you ask Microsoft SQL server how old you are things can get complicated indeed!

So for most men a simple mssql datediff(yy,birthdate,getdate()) is sufficient even though if you were born say on 29.12.1971 it would calculate your age as 36 even though you are still really only 35. You won't be 36 until December 29!

Insurance companies and most women I know tend to get a little upset when you go adding years onto age. So in this instance using datediff doesn't cut it. When the birth date hasn't yet happened in the calendar year you must subtract one day from the age.

In sqlserver you could do something like:

SELECT CASE WHEN CAST(CAST(DATEPART(day, birthdate) AS varchar) + '.' + CAST(DATEPART(month, birthdate) AS varchar)
+ '.' + CAST(DATEPART(year, GETDATE()) AS varchar) AS datetime) > getdate() THEN datediff(yy, birthdate, getdate()) - 1 ELSE datediff(yy,
birthdate, getdate()) END age

Somewhat complicated no?

Thankfully Coldfusion doesn't have this problem, CF correctly calculates the age using it's DateDiff function. I can only assume that in MSSQL server they are simply subtracting 1971 from 2007 instead of taking the two dates as their numeric equivalents subtracting those and then outputting the results in years.