CODEWITHSUNDEEP
sqloracle11gtimestamp
John C
John C

Reputation: 1835

Compare two dates based on timestamp on Oracle 11g

Am dealing with two tables A and B on Oracle 11g and trying to compare records (A.TRANSACTION_TIMESTAMP and B.LAST_TRANSACTION_TIMESTAMP) based on timestamp on columns with datatype DATE

Based on what i have tried, the below query does not give the expected output and returns records even when TRANSACTION_TIMESTAMP is less than LAST_TRANSACTION_TIMESTAMP

TO_CHAR(TRANSACTION_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') >= 
(SELECT TO_CHAR(LAST_TRANSACTION_TIMESTAMP, 'DD-MON-YYYY  HH24:MI:SS') 
  FROM LAST_RAN_TIME)

Would highly appreciate if anyone can provide some inputs on comparing Dates with timestamp on Oracle 11g

Upvotes: 1

Views: 4755

Answers (1)

Richard Schneider
Richard Schneider

Reputation: 35477

Do not convert with TO_CHAR. Just compare columns directly.

The comparision is failing because you are using strings that start with the day! 

TRANSACTION_TIMESTAMP >= (SELECT LAST_TRANSACTION_TIMESTAMP FROM LAST_RAN_TIME)

Upvotes: 3

Related Questions