Oracle SQL comparing dates -

sql> select * porder ;     oid       bill odate   ------ ---------- ---------     10        200 06-oct-13      4      39878 05-oct-13      5     430000 05-oct-13     11        427 06-oct-13     12        700 06-oct-13     14      11000 06-oct-13     15      35608 06-oct-13     13      14985 06-oct-13      8      33000 06-oct-13      9        600 06-oct-13     16        200 06-oct-13     oid       bill odate ------ ---------- ---------      1        200 04-oct-13      2      35490 04-oct-13      3       1060 04-oct-13      6        595 05-oct-13      7        799 05-oct-13  16 rows selected.     sql> select * porder odate='04-oct-2013';  no rows selected 

please help...why isn't query working..??

in oracle, date has time component. client may or may not display time component, still there when try equality comparison. want compare dates dates rather strings use current session's nls_date_format doing implicit conversions making them rather fragile. involve either ansi date literals or explicit to_date calls

you can use trunc function truncate date midnight

select *   porder  trunc(odate) = date '2013-10-04' 

or can range comparison (which more efficient if can benefit index on odate)

select *   porder  odate >= to_date( '04-oct-2013', 'dd-mon-yyyy' )    , odate <  to_date( '05-oct-2013', 'dd-mon-yyyy' );