March 31st, 2004, 01:39 AM
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;
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
March 31st, 2004, 03:21 AM
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 -
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'
March 31st, 2004, 01:37 PM
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' ;
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.
Last edited by jimbag8; March 31st, 2004 at 01:42 PM.
April 1st, 2004, 02:59 AM
Try this :
select order_id from orders