Home » SQL & PL/SQL » SQL & PL/SQL » Select where date equal today's date (oracle 9i)
Select where date equal today's date [message #352221] Mon, 06 October 2008 12:20 Go to next message
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 #352222 is a reply to message #352221] Mon, 06 October 2008 12:29 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
DATE columns have time components to them.
You can use TRUNC to ignore the time portion.

[edit]

Oh, of course I didn't read the non-code part of your post.

You can create an function based index on the TRUNC of the column to speed up your queries.

[Updated on: Mon, 06 October 2008 12:31]

Report message to a moderator

Re: Select where date equal today's date [message #352313 is a reply to message #352222] Tue, 07 October 2008 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Select where date equal today's date [message #352317 is a reply to message #352221] Tue, 07 October 2008 01:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Frank means to say ,

to use

Quote:
where date between <your_date> and <your_date + 1 - 1 / (24 * 60 * 60)


I got confused at the first glance Smile

Thumbs Up
Rajuvan.
Re: Select where date equal today's date [message #352323 is a reply to message #352317] Tue, 07 October 2008 02:14 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer:

where date >= "your_date" and date < "your_date"+1

clearler than "- 1 / (24 * 60 * 60)". Just my 2 cents.

Regards
Michel
Previous Topic: problem in outer join query
Next Topic: How to Pass array of values in Stored Procedures
Goto Forum:
  


Current Time: Wed Dec 07 18:55:08 CST 2016

Total time taken to generate the page: 0.09825 seconds