Saobi Saobi - 1 year ago 209
SQL Question

Passing date parameters to Oracle Query in SSRS

I have an SSRS report that uses an Oracle datasource. I have a query;

select * from myTable t where between Date '2009-08-01' and Date '2009-08-04' + 1

This query works, both in Oracle and from SSRS. It gets all rows from myTable when the column is between 08/01/2009 and 08/04/2009. The column is of type Date()

Now, I want the dates to be parameters. So I changed the query to:

select * from myTable t where between Date :myDate and Date :myDate + 1

When I run this query in SSRS, it prompts me to enter a value for :myDate.

I tried 2009-08-01, '2009-08-01' both results in an oracle sql processing error: " Missing expression".

Any ideas?

Answer Source

If you insist on having one parameter (:myDate), using a with clause can make the process a bit cleaner. I'm not familar with the syntax you have in your example (i.e. the 'Date' like casting in the query), below is a SQL only implementation.

 select TRUNC(TO_DATE(:mydate, 'yyyy-mm-dd')) p_date from dual as parameters
select t.* 
  myTable t, 
  trunc( between parameters.p_date and parameters.p_date + 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download