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

Hi,

substracting two timestamps will return a 20,6 numeric value, with the
following format YYYYMMDDHHMMSS,MSMSMS

If you want to calculate the time difference in hours and if you are already
on release V5R4M0, you may use the scalar function TIMESTAMPDIFF.
Im not sure what the format of RCVRDT is. If it's only a date the scalar
function TIMESTAMP will set the time portion of the timestamp to 00.00.00.
If RCVRDT is a time, the date portion of the timestamp will be set to
current date. 
 
The following example shows how the scalar function TimeStampDiff can be
used to calculate the difference between the current time and a timestamp.

Select TimeStampDiff(8, cast(current_timestamp - MyTimeStamp as Char(22))), 
       a.*
   from MyTable
   Where TimeStampDiff(8, cast(current_timestamp - MyTimeStamp 
                               as Char(22))) > 8

An easier way would be to write a small RPG-Function and register this
funcation as UDF.

Birgitta
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
todd.sabella@xxxxxxxxxxxxx
Gesendet: Dienstag, 27. Juni 2006 15:04
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: SQL - Calculate the number of hours between 2 timestamps





Folks,

With SQL,I am trying to determine if the time stamp value in a field
(RVCRDT) within a file (RTPRVER) is more than 8 hours old.

SELECT hour(NOW() - timestamp(RVCRDT)) , timestamp(RVCRDT) as create_date,
NOW()
FROM RTPRVER
WHERE hour(NOW() -  timestamp(RVCRDT)) >= 8

I thought that the HOUR function would give me what I needed, but it seems
to be just subtracting the hours portions of the timestamp fields.

Here's some results that I'm getting.  Obviously, I'm missing something

HOUR   CREATE_DATE            NOW ( )
   8  2006-01-02-00.46.36.000000    2006-06-27-08.47.19.481856
   8  2006-02-12-00.31.37.000000    2006-06-27-08.47.19.481856
   8  2006-02-13-00.17.26.000000    2006-06-27-08.47.19.481856
   8  2006-02-13-00.17.37.000000    2006-06-27-08.47.19.481856
   8  2006-02-13-00.24.35.000000    2006-06-27-08.47.19.481856
   8  2006-02-13-00.36.11.000000    2006-06-27-08.47.19.481856

Any help would be appreciated!!!!!
Thanks
Todd
--
"NOTICE:  The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential.  If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected."