June 28, 2008 at 7:11 pm
Comments posted to this topic are about the item Calculating Age
June 30, 2008 at 4:06 am
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.
June 30, 2008 at 4:27 am
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 !
June 30, 2008 at 4:53 am
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/.
June 30, 2008 at 5:54 am
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
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 30, 2008 at 6:20 am
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
June 30, 2008 at 7:24 am
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" ?
June 30, 2008 at 7:52 am
http://www.sqlteam.com/article/datediff-function-demystified
N 56°04'39.16"
E 12°55'05.25"
June 30, 2008 at 8:12 am
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.
😎
June 30, 2008 at 8:16 am
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.
😎
June 30, 2008 at 8:38 am
I got this error from that code:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '–'.
June 30, 2008 at 9:09 am
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.
June 30, 2008 at 11:33 am
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
June 30, 2008 at 12:05 pm
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?
😎
June 30, 2008 at 12:07 pm
That worked! thanks!
Viewing 15 posts - 1 through 15 (of 95 total)
You must be logged in to reply to this topic. Login to reply