Passing date parameters to Oracle Query in SSRS

Question

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

select * from myTable t where
t.date 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 t.date column is between 08/01/2009 and 08/04/2009. The t.date column is of type Date()

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

select * from myTable t where
t.date 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 1

Have you tried making sure the parameter is a DateTime type (it is string by default) in the Report Parameters menu?

Answer 2

Just because you have used :myDate twice doesn't mean that they are the same. They are placeholders, and since you have two placeholders you need two values.

Answer 3

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.

with
 select TRUNC(TO_DATE(:mydate, 'yyyy-mm-dd')) p_date from dual as parameters
select t.* 
from 
  myTable t, 
  parameters 
where
  trunc(t.date) between parameters.p_date and parameters.p_date + 1