View Single Post
 
Old February 4th, 2004, 12:51 PM
azizmasih azizmasih is offline
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mcmouli,

You need to compare the date with (sysdate - 50/24) i.e. you need to convert 50 hours into days first. The following execution sequence shows how you can use "decode(sign())" function to compare the dates in the dummy table t.

----------------------------------------------------------------------
SQL> select to_char(dt, 'fmDay, dd Month yyyy hh24:mi:ss') "Date" from t;

Date
-------------------------------------
Thursday, 1 January 2004 15:15:30
Tuesday, 3 February 2004 10:34:45

SQL> select
  2 to_char(dt, 'fmDay, dd Month yyyy hh24:mi:ss') "Date" ,
  3 to_char(sysdate, 'fmDay, dd Month yyyy hh24:mi:ss') "Sysdate - 50 hours",
  4 decode( sign(dt - (sysdate - 50/24)),
  5 -1, 'Date is less than sysdate - 50 hours',
  6 1, 'Date is greater than sysdate - 50 hours',
  7 'Date is equal to sysdate - 50 hours') "Message"
  8 from t
  9 /

Date Sysdate - 50 hours Message
------------------------------------- ------------------------------------- ---------------------------------------
Thursday, 1 January 2004 15:15:30 Wednesday, 4 February 2004 10:37:39 Date is less than sysdate - 50 hours
Tuesday, 3 February 2004 10:34:45 Wednesday, 4 February 2004 10:37:39 Date is greater than sysdate - 50 hours

SQL>

----------------------------------------------------------------------

Hope that helps.

Cheers,
azizmasih