Calculating Age

• Comments posted to this topic are about the item Calculating Age

• You've identified one (of the two) most common ways to calculate age. The other one being the "I'm one year older if my birthday is today or was earlier this year" algorithm. See my blog (http://blog.tkbe.org/archive/python-how-old-are-you/) for code and discussion of the issues.

• Hi everyone !

I use this formula:

Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))

You can also put it in a calculated column.

It works (I think) because one year actually has 365.25 days... Like we only count 365 days in a regular year, we need compensate with an extra day every four years (leap year).

Try it and let me know how it goes !

• This issue was heavily debated as a result of the errorous QotD a few months back...

Several solutions was proposed, most of them partially correct, however the February 29. issue may be subject to different policies in different regions.

The QotD can be found here: http://www.sqlservercentral.com/questions/T-SQL/62373/.

• Similar to Sergio's solution, mine breaks down an age into quarterly periods (three months), also accounting for leap year, so partial ages can be compared:

ALTER FUNCTION [dbo].[fnCalcAge] (@DOB datetime, @CurrentDate datetime) RETURNS real

AS

BEGIN

RETURN FLOOR(((DATEDIFF(dd, @DOB, @CurrentDate) +

CASE WHEN DATEPART(mm, @DOB) = DATEPART(mm, @CurrentDate) AND

DATEPART(dd, @DOB) = DATEPART(dd, @CurrentDate)

THEN 1 ELSE 0 END) / 365.25) / .25) * .25

END

• I have used the following function for some time in insurance database without issues. In this application, age is assumed to be a whole number in years (i.e. Never aged 5.2 years just 5 or 6). Simply put, you only reach your full age on the annivesary of your birthday.

Create function dbo.GetAge

(@BirthDate datetime, @CalcDate datetime)

Returns int

As

begin

declare @theAge int

If Month(@CalcDate) > Month(@BirthDate )

select @theAge= (Year(@CalcDate) - Year(@BirthDate ))

Else

If Month(@CalcDate) < Month(@BirthDate )

select @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1)

Else

If Day(@CalcDate) < Day(@BirthDate )

select @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1)

Else

select @theAge= (Year(@CalcDate) - Year(@BirthDate ))

return @theAge

end

• The final SQL didn't work for me, QA returned an error about the hyphen.

Also, I'm not that experienced but surely it's "case when" and not "casewhen" ?

• http://www.sqlteam.com/article/datediff-function-demystified

N 56°04'39.16"
E 12°55'05.25"

• To all those who are noticing some issues with some of the code in the article. I will contact Steve about making some corrections. The code in the original file submitted does not have the issues reported, so it may have been a "typesetting" issue as the article was reformatted for publication.

My original code:

`declare @dob datetime,`

` @age int,`

` @day datetime`

`set @day = '2008-02-28'`

`set @dob = '2007-03-01'`

`set @age = datediff(yy,@dob,@day) –`

` case when @day < dateadd(yy, datediff(yy,@dob,@day), @dob) then 1 else 0 end`

`select @age`

Please let me know if this code has a problem.

😎

• Peter,

I just took the time to read your article above. I wish I had know about it as I was doing my article, I would have put a link to it in mine so that others could get more information about DATEDIFF and date arithmetic. Very well done and helpful.

😎

• I got this error from that code:

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '–'.

• Try deleting the hyphen and re-entering it. Sometimes converting from SSMS to Word to our editor and HTML gets strange replacements for characters. I think this was one of those cases.

• The posted codes work fine for full years, but I have been trying to find good SQL code that will calculate down to days if needed (for babies). I a baby is under a couple weeks old, I need to report how many days old they are. If the baby is older than that, but under a few months old, I need to report how many weeks old they are. If the child is older than a few months, but less than a year, I have to report how many months old they are. I haven't been able yet to come up with code that will do that and display something like (as applicable):

3d or

2w or

4m or (if older)

16y

• Peggy Rowles (6/30/2008)

The posted codes work fine for full years, but I have been trying to find good SQL code that will calculate down to days if needed (for babies). I a baby is under a couple weeks old, I need to report how many days old they are. If the baby is older than that, but under a few months old, I need to report how many weeks old they are. If the child is older than a few months, but less than a year, I have to report how many months old they are. I haven't been able yet to come up with code that will do that and display something like (as applicable):

3d or

2w or

4m or (if older)

16y

Your requirements above a slightly vague. Could you provide firmer requirements on what needs to be reported, and how you would want the data returned?

😎

• That worked! thanks!

Viewing 15 posts - 1 through 15 (of 93 total)

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