We help IT Professionals succeed at work.

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

kravindra
kravindra asked
on
538 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.

Comment
Watch Question

Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
select * from APP_SWIFT_INFO where trunc(CREATED_DATE) = to_date('04/15/2011','mm/dd/yyyy');
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Commented:
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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>>  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

Commented:
Thanks I learned something today :)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
kravindra,

the accepted answer does NOT give the results you requested
please explain why that answer was chosen
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.