SQL thats give me someting 4 hours old

[Larshg]

Hi

Small problem - I want to make this SQL sentense more dynamic - right now I'm manualy updating the script to be 4 hours before systime.

Select count(*)
FROM prepaid.sms_rating_transactions_v4
where trx_status = 'PE'
and CHANNEL_TIME < to_date('11-06-2002 10:27','DD-MM-YYYY HH24:MI');


11-06-2002 10:27 - this is the time that always should be 4 hours old(systime - 4 hours).

Thanks

 

[PruSQLer]

As with so many of these issues, &quot;it depends on your DBMS&quot;. In DB2, I would use the TIMESTAMP function:

Select count(*)
From Table
Where Channel_Time < TIME(CURRENT TIMESTAMP) - 4 hours

What's the DBMS? I'm sure someone in this forum will know the function.

 

[grega]

In Sybase (and probably SQL Server) the syntax would be ..

Select count(*)
From Table
Where Channel_Time < dateadd(hh,-4,getdate())

Greg.

 

[karluk]

The to_date function looks like Oracle, so you can reference the current system date and time with SYSDATE. Rewriting your query using sysdate gives

Select count(*)
FROM prepaid.sms_rating_transactions_v4
where trx_status = 'PE'
and CHANNEL_TIME < sysdate - 4/24;