Calculating Age

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lynn Pettis » Calculating Age

90 posts, Page 1 of 9

1 2 3 4 5 »»»

Calculating Age

Rate Topic

Display Mode

Topic Options

Author

Message

Lynn Pettis

Posted Saturday, June 28, 2008 7:11 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 21,589, Visits: 27,387

Comments posted to this topic are about the item Calculating Age

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #525559

Bjorn Pettersen

Posted Monday, June 30, 2008 4:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 20, 2012 1:34 AM
Points: 4, Visits: 22

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.

Post #525783

Sergio Lugo

Posted Monday, June 30, 2008 4:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 13, 2009 4:50 PM
Points: 5, Visits: 11

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 !

Post #525792

Øystein Fallo

Posted Monday, June 30, 2008 4:53 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 8:38 AM
Points: 1,419, Visits: 420

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/.

Post #525798

Aaron N. Cutshall

Posted Monday, June 30, 2008 5:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 9:57 AM
Points: 895, Visits: 504

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

"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17

Post #525824

James Taylor-334331

Posted Monday, June 30, 2008 6:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 13, 2009 3:42 AM
Points: 1, Visits: 12

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

Post #525848

Sharon Matyk

Posted Monday, June 30, 2008 7:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 26, 2010 10:00 AM
Points: 92, Visits: 47

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

Post #525900

SwePeso

Posted Monday, June 30, 2008 7:52 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292

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

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

Post #525929

Lynn Pettis

Posted Monday, June 30, 2008 8:12 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 21,589, Visits: 27,387

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #525961

Lynn Pettis

Posted Monday, June 30, 2008 8:16 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 21,589, Visits: 27,387

Peso (6/30/2008)

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

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #525965

« Prev Topic | Next Topic »

90 posts, Page 1 of 9

1 2 3 4 5 »»»

Permissions