How can I query rows using a date field properly?

Posted on 2008-06-26
Last Modified: 2013-12-19
This is my line of code (which doesn't work), how can this modified to retrieve rows?

AND CBPV.BP_START_DATE >= to_date(:bp_period_start,'DD/MM/YYYY')
AND CBPV.BP_START_DATE <= to_date(:bp_period_end,'DD/MM/YYYY')

bp_start_date is a date field type, sample data
6/1/2008 , 4/23/2008

Question by:Rhonda Carroll
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 21875409
Make sure the values used for the parameters are strings and not date.

LVL 44

Expert Comment

ID: 21877522
is the field  CBPV.BP_START_DATE a DATE type field, or a Varchar field?

you might try this (if CBVP.BP_START_DATE is a DATE field):

AND TO_CHAR(CBPV.BP_START_DATE, 'YYYYMMDD') >= to_char(:bp_period_start,'YYYYMMDD')
AND TO_CHAR(CBPV.BP_START_DATE, 'YYYYMMDD') <= to_char(:bp_period_end,'YYYYMMDD')


LVL 10

Accepted Solution

dbmullen earned 125 total points
ID: 21880518
a date is a date is a date, regardless of how you display it.  it's saved as a DATE

bp_period_start date := to_date('20080101','yyyymmdd');
bp_period_end date := trunc(sysdate);

select *
from ...
where ...
AND CBPV.BP_START_DATE >= :bp_period_start
AND CBPV.BP_START_DATE <= :bp_period_end

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

752 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