Calculating age from birthday

Question

Post reply

Calculating age from birthday

Bill Walker

Old Hand

Points: 362
More actions
February 20, 2006 at 10:35 am

#167114

I'm trying to create an expression field in report services that will calculate a persons age based on a date of birth field.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

Scott Coleman

One Orange Chip

Points: 27452

February 20, 2006 at 10:42 am

#622084

datediff

(yy,DOB,getdate())

Bill Walker Old Hand Points: 362 More actions · Answer 2

Thanks, when I place it into the expression it says that yy is not declared.

Sorry to ask the stupid questions

Bill Walker Old Hand Points: 362 More actions · Answer 3

Bill Walker

Old Hand

Points: 362

February 20, 2006 at 10:56 am

#622087

Nevermind, I got it to work, thanks

DanKennedy Hall of Fame Points: 3736 More actions · Answer 4

That won't work when a persons birthday has not yet occurred in the current year e.g. someone born on 1st April 1973 would be 33 according to your function now whereas in reality they are not 33 until the 1st April 2006. An additional check needs to be done to see whether this years birthday has occurred yet and if not subtract one year.

Dan
www.firstcs.co.uk

Parker Smith SSChasing Mays Points: 619 More actions · Answer 5

About the fastest way is to use the following:

Floor(DateDiff(dd, DOB, GetDate())/365.25)

Dividing by 365.25 allows for leap years, and taking the 'Floor' gives you the integer part of the value.

There is a small chance you could get the wrong value on a person's birthday, but I have not been able to find such a case (though I would be interested to see one).

For most purposes this should be sufficiently accurate - fortunately, the year 2000 was a leap year (the 400 year rule on leap years came into effect - 1900 and 2100 are NOT leap years (the 100 year rule).

vadba SSChampion Points: 11133 More actions · Answer 6

Actually, SELECT Floor(DateDiff(dd, @DOB, GetDate())/365.25) fails for most dates that are one day behind (see February 20 example - today is currently 2006-02-21). But it is fast otherwise.

You could also try something like:

DECLARE @dob datetime

SET @dob = '2/20/1969'

SELECT Datediff(yy, @dob , GetDate())

- CASE

WHEN CONVERT(datetime, Stuff(CONVERT(varchar(8), @dob, 112), 1, 4, CONVERT(varchar(4), Year(GetDate())))) < CONVERT(datetime, CONVERT(varchar(8), GetDate(), 112))

THEN 1

ELSE 0

END AS Age

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

BOL for SQL 2005 says:

"Returns the number of date and time boundaries crossed between two specified dates."

I took them at their word. Silly me. Although a careful reading of the description for the datepart parameter ("Is the parameter that specifies on which part of the date to calculate the difference.") indicates that it truncates both dates to the time unit requested before subtracting.

This version works:

select

case when dateadd(yy,Age,DOB)>getdate() then Age-1 else Age end

from (

select DOB, datediff(yy,DOB,getdate()) as Age

from (

select '1973-04-01' as DOB

) x

) y