RE: AW: SQL - Calculate the number of hours between 2 timestamps

I have not had to do this kind of calculation, but that's a real gotcha. It
looks like the best way would be to split it out into date and time
portions. Take the number of days difference between the two dates, multiply
by 24, then add to that the difference in hours between the two times?

Francis Lapeyre
IS Dept. Programmer/Analyst
Stewart Enterprises, Inc.
E-mail: flapeyre@xxxxxxxx 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, June 27, 2006 11:00 AM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL - Calculate the number of hours between 2 timestamps

Study that timestampdiff bif carefully.  A lot of assumptions are made. 
Which may, or may not, matter for your situation.  Straight from the book:

The following assumptions may be used in estimating the difference: 

there are 365 days in a year 
there are 30 days in a month 
there are 24 hours in a day 
there are 60 minutes in an hour 
there are 60 seconds in a minute
These assumptions are used when converting the information in the second 
argument, which is a timestamp duration, to the interval type specified in 
the first argument. The returned estimate may vary by a number of days. 
For example, if the number of days (interval 16) is requested for a 
difference in timestamps for '1997-03-01-00.00.00' and 
'1997-02-01-00.00.00', the result is 30. This is because the difference 
between the timestamps is 1 month so the assumption of 30 days in a month 
applies.


Rob Berendt