Date Calculations
-
Hi,
I believe the following code is correct. Can anyone say it isn't ? I'd be happy to be proven wrong:
Declare
@BirthDate Datetime
, @DateOfWildParty DateTime
, @AgeAtWildParty Int
Set @BirthDate = '05/01/1950'
Set @DateOfWildParty = '11/07/2005'
Set @AgeAtWildParty = DateDiff(dd, @BirthDate, @DateOfWildParty) / 365
RaisError('Age at time of wild party was %d', 0, 1, @AgeAtWildParty)
-
Well it produces the right results, but I would question why you'd use a calculation based on the number of days when you could just use yy instead.
Set @AgeAtWildParty = DateDiff(yy, @BirthDate, @DateOfWildParty)
Cheers
-
Never mind. I see where it can account for when your birthday is. I did find that it doesn't seem to get down to the day level though. You can play with Birthdates of 11/06/1950, 11/07/1950, and 11/08/1950 and it doesn't change the results.
Cheers
-
Hi Mark,
Thank you for your reply. I used that approach a long time ago, and I believe I found an issue with it.
Can't prove it however.
Richard
-
declare @table table
(
birthdate datetime,
today datetime
)
insert into @table
select '1950-05-01', '2005-11-07' union all
select '1950-12-01', '2005-11-07' union all
select '2004-12-31', '2005-01-01'
select birthdate = convert(varchar(10), birthdate, 121),
today = convert(varchar(10), today, 121),
method_1 = datediff(year, birthdate, today),
method_2 = case when today > dateadd(year, year(today) - year(birthdate), birthdate) then
datediff(year, birthdate, today)
else
datediff(year, birthdate, today) - 1
end
from @table
-
Yes, it is wrong... it does not account for Leap Years.
The following shows an age of 55 when the real age is 2 WEEKS less than what is shown...
Declare
@BirthDate Datetime
, @DateOfWildParty DateTime
, @AgeAtWildParty Int
Set @BirthDate = '11/21/1950'
Set @DateOfWildParty = '11/07/2005'
Set @AgeAtWildParty = DateDiff(dd, @BirthDate, @DateOfWildParty) / 365
RaisError('Age at time of wild party was %d', 0, 1, @AgeAtWildParty)
Here's one of the many methods to account for Leap Years when calculating ages...
SET @AgeAtWildParty = YEAR(DATEADD(dd,DATEDIFF(dd,@BirthDate,@DateOfWildParty),0)-1)-1900
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Or just:
Set @AgeAtWildParty = DateDiff(dd, @BirthDate, @DateOfWildParty) / 365.25
_____________
Code for TallyGenerator -
Uhhg... I know you're right and it works great, but I just can't bring myself to do it
because way deep down inside, I know a year is not exactly 365.25 days... I 'spose I'll get over that one of these days 
...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Neither one of the two values is precise, but actually a year is closer to 365.25 days than to 365. It is just that we are used to have years with whole number of days, which forces us to add one day here and there. When talking about someone's precise age in years and days, you calculate years separately from days : years = how many full years have elapsed (number of birthdays), days = how many days since the last birthday. Although you can do that in SQL, too, you don't have to. Using / 365.25 is much more simple and gives correct results (as well as Jeff's solution).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply