Select where date equal today's date [message #352221] |
Mon, 06 October 2008 12:20  |
jriggs
Messages: 12 Registered: October 2007
|
Junior Member |
|
|
Having a problem selecting rows based in the date.
SQL> select value from nls_database_parameters
2 where parameter = 'NLS_DATE_FORMAT' ;
DD-MON-RR
SQL> select datemanifested from orders where ordernum='R00000475537';
01-OCT-08
So then I tried
SQL> select ordernum from orders where datemanifested = to_date('01-OCT-2008','DD-MON-YYYY');
no rows selected
SQL> select ordernum from orders where datemanifested = to_date('01-OCT-08','DD-MON-YY');
no rows selected
SQL> select ordernum from orders where datemanifested = to_date('01-OCT-08','DD-MON-RR');
no rows selected
The only thing that I could get to work was adding "trunc(datemanifested)", but this caused the query to take quite a while to execute.
Eventually, I would like an efficient way to select all rows where datemanifested = sysdate (date today). Thanks for any insight-
jr
|
|
|
|
Re: Select where date equal today's date [message #352313 is a reply to message #352222] |
Tue, 07 October 2008 01:09   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
joy_division wrote on Mon, 06 October 2008 19:29 |
You can create an function based index on the TRUNC of the column to speed up your queries.
|
Either that or search for records where date between <your_date> and <your_date + 1 - 1 / (24 * 60 * 60)>
That will lose the function on the date, so the index can be used again.
|
|
|
|
|