SQL 将日期参数传递给 SSRS 中的 Oracle 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1229575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing date parameters to Oracle Query in SSRS
提问by Saobi
I have an SSRS report that uses an Oracle datasource. I have a query;
我有一个使用 Oracle 数据源的 SSRS 报告。我有一个疑问；
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()
此查询适用于 Oracle 和 SSRS。当 t.date 列在 08/01/2009 和 08/04/2009 之间时，它从 myTable 获取所有行。t.date 列的类型为 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.
当我在 SSRS 中运行此查询时，它提示我输入 :myDate 的值。
I tried 2009-08-01, '2009-08-01' both results in an oracle sql processing error: " Missing expression".
我尝试了 2009-08-01，'2009-08-01' 都导致 oracle sql 处理错误：“缺少表达式”。
Any ideas?
有任何想法吗？
回答by Brian
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.
如果您坚持使用一个参数 (:myDate)，则使用 with 子句可以使过程更简洁一些。我不熟悉您在示例中的语法（即查询中的“日期”之类的转换），下面是仅 SQL 的实现。
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
回答by mihir pandya
This worked for me using SSRS:
这对我使用 SSRS 有用：
SELECT t.*
FROM myTable t
WHERE t.date between :myDate AND to_date(:myDate + 1)
回答by Gary Myers
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.
仅仅因为您使用了 :myDate 两次并不意味着它们是相同的。它们是占位符，因为您有两个占位符，所以需要两个值。
回答by pfunk
Have you tried making sure the parameter is a DateTime type (it is string by default) in the Report Parameters menu?
您是否尝试过确保“报告参数”菜单中的参数为 DateTime 类型（默认为字符串）？
回答by RET
I had a similar problem with a much larger query, and I found that simply searching and replacing e.g. :mydate with TO_DATE(:mydate) was sufficient to solve the problem - the same date was then passed to every instance of TO_DATE(:mydate) in the query.
我在一个更大的查询中遇到了类似的问题，我发现简单地搜索和替换例如 :mydate 与 TO_DATE(:mydate) 就足以解决问题 - 然后将相同的日期传递给 TO_DATE(:mydate) 的每个实例在查询中。
If you want a date range passed into the query, I would give the parameters different names e.g. :StartDate and :EndDate in the SQL and have separate parameters @StartDate and @EndDate in the report. This makes the SQL much clearer.
如果您希望将日期范围传递给查询，我会给参数不同的名称，例如：SQL 中的 :StartDate 和 :EndDate，并在报告中使用单独的参数 @StartDate 和 @EndDate。这使得 SQL 更加清晰。
回答by Benny Mathew
SSRS newer versions :parameter is not working, you need to use ? as below
SSRS 较新版本：参数不起作用，您需要使用 ? 如下
below first two parameters date and third one text select * from tasks where FINISH_DATE between ? and ?+1 and t2.TYPE = ?
下面的前两个参数日期和第三个文本 select * from tasks where FINISH_DATE between ? 和 ?+1 和 t2.TYPE = ?
