Compare two dates using timestamp in oracle

Question

select * 
  from  table1 rcd, 
        table2 company 
 WHERE to_timestamp(to_char(rcd.date), 'YYYY-MM-DD HH:MI:SS') >
       to_timestamp(to_char(company.date), 'YYYY-MM-DD HH:MI:SS')

I want to compare two of my date fields using the timestamp (to_timestamp) in oracle and I am using above query its not giving correct results (giving records which are not greater than from company.date).

any idea how to compare using the to_timestamp in oracle.

You don't appear to have joined table1 to table2, so you'll get a cartesian product. Is this what you meant? — paul
– paul, Commented Sep 17, 2013 at 10:01
> can compare time stamps, but you try to select from cartesian producted result set. — erencan
– erencan, Commented Sep 17, 2013 at 10:24
@GordonLinoff it works when we direct compare them. but when we use timestamp i think it should also do the same considering the time along with date. — vikasgupta
– vikasgupta, Commented Sep 17, 2013 at 12:26

Gordon Linoff · Accepted Answer · 2013-09-17 10:53:42Z

5

to_char(<date>) does not produce the format you are using for to_timestamp(). The safest way would be to repeat the format argument:

WHERE to_timestamp(to_char(rcd.date, 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') >
      to_timestamp(to_char(company.date, 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS')

Wait. The safest is to forego all these conversions, and just do:

select * 
from table1 rcd join
     table2 company 
     on rcd.date > company.date;

answered Sep 17, 2013 at 10:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Edgar O Over a year ago

Thks, it's useful

Suresh Over a year ago

rcd.date > to_timestamp(to_char('2018-07-30 17:10:46', 'yyyy-MM-dd HH:MI:SS'), 'yyyy-MM-dd HH:MI:SS') is throwing me error ORA-01722: invalid number. Any idea why?

Suresh Over a year ago

Even this one is also throwing the same error... rcd.date > to_timestamp(to_char('2018-07-30 17:10:46', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS')

Collectives™ on Stack Overflow

Compare two dates using timestamp in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related