Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Calculating Age Expand / Collapse
Author
Message
Posted Saturday, June 28, 2008 7:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 22,994, Visits: 31,476
Comments posted to this topic are about the item Calculating Age


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
Posted Monday, June 30, 2008 4:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 30, 2008 4:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 30, 2008 4:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 2:50 AM
Points: 1,419, Visits: 439
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
Posted Monday, June 30, 2008 5:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:40 AM
Points: 1,090, Visits: 540
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
Posted Monday, June 30, 2008 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 30, 2008 7:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Monday, June 30, 2008 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:22 AM
Points: 2,393, Visits: 3,398
http://www.sqlteam.com/article/datediff-function-demystified


N 56°04'39.16"
E 12°55'05.25"
Post #525929
Posted Monday, June 30, 2008 8:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 22,994, Visits: 31,476
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.




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 #525961
Posted Monday, June 30, 2008 8:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 22,994, Visits: 31,476


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.




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 #525965
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse