Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I query rows using a date field properly?

Posted on 2008-06-26
3
Medium Priority
?
254 Views
Last Modified: 2013-12-19
Hi,
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

Thanks,
R
0
Comment
Question by:Rhonda Carroll
3 Comments
 

Expert Comment

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

0
 
LVL 44

Expert Comment

by:Arthur_Wood
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')


AW


0
 
LVL 10

Accepted Solution

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

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

select *
from ...
where ...
AND CBPV.BP_START_DATE >= :bp_period_start
AND CBPV.BP_START_DATE <= :bp_period_end
...
;
end;
/
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

885 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