Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to search data by date in where clause in PL/SQL

Posted on 2011-04-19
9
Medium Priority
?
489 Views
Last Modified: 2012-05-11
select * from APP_SWIFT_INFO where CREATED_DATE = to_date('04/15/2011','mm/dd/yyyy');

I want to get data by date field, could you tell me please how to get the data by date field and what is wrong in  the above query.

In my database data in the date field like this  
4/19/2011 2:06:18 PM

Here I want to send only the date and read the corresponding record.

0
Comment
Question by:kravindra
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 35427736
select * from APP_SWIFT_INFO where trunc(CREATED_DATE) = to_date('04/15/2011','mm/dd/yyyy');
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35427814
if create_date has an index on it, you'll want to do a range based search

select * from APP_SWIFT_INFO
where CREATED_DATE >= to_date('04/15/2011','mm/dd/yyyy')
and create_date < to_date('04/15/2011','mm/dd/yyyy') + 1


0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35428043
Other way of writing of what sdstuber said


select * from APP_SWIFT_INFO 
where CREATED_DATE BETWEEN to_date('04/15/2011','mm/dd/yyyy')
AND to_date('04/15/2011','mm/dd/yyyy') + 1

Open in new window

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35428607
NO,  BETWEEN is not the same thing


between is   >=   <=  which means you'd include 4/16/2001  00:00:00

I used   >=  <   on purpose to exlude midnight of the following day

if you wanted to use between then you'd have to substract 1 second
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35428626
Great Sean.

You are 100% correct.  I use BETWEEN more often as I believe, performance wise its better.

kravindra, ignore my comment as its erroneous
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35428664
>>>  as I believe, performance wise its better.

nope

check your explain plans,  BETWEEN is resolved to >=  <= to filter result sets there is no "BETWEEN" operator in the CBO

sort of like there really isn't an "IN" filter to the CBO either
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35428673
Thanks I learned something today :)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35795064
kravindra,

the accepted answer does NOT give the results you requested
please explain why that answer was chosen
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question