Date Calculations

Question

Date Calculations

RLSQL

Say Hey Kid

Points: 713
More actions
March 31, 2006 at 11:43 am

#112781

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)

Viewing 9 posts - 1 through 8 (of 8 total)

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

AlwaysLearning SSChasing Mays Points: 627 More actions · Answer 1

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

AlwaysLearning SSChasing Mays Points: 627 More actions · Answer 2

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

RLSQL Say Hey Kid Points: 713 More actions · Answer 3

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

[KH] SSCommitted Points: 1908 More actions · Answer 4

declare@table table

(

birthdatedatetime,

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'

selectbirthdate = 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

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 5

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Or just:

Set @AgeAtWildParty = DateDiff(dd, @BirthDate, @DateOfWildParty) / 365.25

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 7

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)

Vladan SSC-Insane Points: 22072 More actions · Answer 8

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