How do u select a date in oracle???

When i type what is below, i get the order_entrydate returned for that ID. This order_entrydate was generated by sysdate in my asp code to oracle.

SQL> select order_entrydate from orders where order_id=1;

ORDER_ENT
---------
31-MAR-04


However, when i do it the opposite way, there are no rows selected. I tried various formats for the date, but i cant seem to return anything. what went wrong??

SQL> select order_id from orders where order_entrydate='31-Mar-04';

no rows selected

Hi,

When you do a date-'14-3-2004' it tries to match the date along with the hour (which in this case is 00:00:00) whereas the date column in the table is in a different hour/minut/second -

try

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'

and repeat.

Or, and a left side function like this is not optionmal, but you could just make your where clause
trunc(order_date) = '31-MAR-04' ;

*or*

use a between...

order_date between (trunc(order_date))
and (trunc(order_date + 1))



You may need to use a hint in the select if there are a lot of records and you decide to go with the left side trunc. This will do some consintent gets and physical reads.

Hi,

Try this :

select order_id from orders
where order_entrydate=to_date('31-Mar-04');