SSRS - Oracle DB,传递日期参数
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3037996/
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
SSRS - Oracle DB, Passing Date parameter
提问by David
Using SSRS with an Oracle Database. I need to prompt the user when running the report to enter a date for report. What is the best way to add in the parameter in my SSRS Report. Having problem finding the right date format. under the "Report Parameter" menu, I have setup the Report Parameters using the DateTime Datatype.
将 SSRS 与 Oracle 数据库结合使用。我需要在运行报告时提示用户输入报告日期。在我的 SSRS 报告中添加参数的最佳方法是什么。找到正确的日期格式时遇到问题。在“报告参数”菜单下,我使用 DateTime 数据类型设置了报告参数。
Keep getting this error "ORA-01843: Not a Valid Month"
不断收到此错误“ORA-01843:不是有效月份”
Thank you for your help.
感谢您的帮助。
Select
a.OPR_Name,
a.OPR,
a.Trans_Desc,
a.Trans_Start_Date,
Cast(a.S_Date as date) as S_Date,
Sum(a.Duration) as T
From (
Select
US_F.OPR_Name,
ITH_F.OPR,
ITH_F.ITH_RID,
ITH_F.TRANSACT,
Transact.DESC_1 as Trans_Desc,
To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') as Trans_Start_Date,
To_CHAR(ITH_F.Start_Time,'MM/DD/YYYY') as S_Date,
Substr(To_CHAR(ITH_F.Start_Time,'HH24:MI'),1,6) as Start_Time,
To_CHAR(ITH_F.End_Time,'DD-Mon-YY') as Trans_End_Date,
Substr(To_CHAR(ITH_F.End_Time,'HH24:MI'),1,6) as End_Time,
Cast(Case When To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') = To_CHAR(ITH_F.End_Time,'DD-Mon-YY')
Then (((To_CHAR(ITH_F.End_Time,'SSSSS') - To_CHAR(ITH_F.Start_Time,'SSSSS')) / 60))/60
Else ((86399 - (To_CHAR(ITH_F.Start_Time,'SSSSS')) + To_CHAR(ITH_F.End_Time,'SSSSS'))/60)/60
End as Decimal(3,1)) as Duration
from Elite_76_W1.ITH_F
Left Join Elite_76_W1.Transact
on Transact.Transact = ITH_F.Transact
Left Join Elite_76_W1.US_F
on US_F.OPR = ITH_F.OPR
Where ITH_F.TRANSACT not in ('ASN','QC','LGOT')
) a
Where a.S_Date = @Event_Date
Having Sum(a.Duration) <> 0
Group By a.OPR_Name,
a.OPR,
a.Trans_Desc,
a.Trans_Start_Date,
a.S_Date
Order by a.OPR_Name
回答by
Oracle parameters are indicated with a leading colon - @Event_Date should be :Event_Date.
Oracle 参数用前导冒号表示 - @Event_Date 应该是 :Event_Date。
回答by Vincent Malgrat
You use CAST(a.S_Date AS DATE)
in your query, where a.S_Date
is a VARCHAR: To_CHAR(ITH_F.Start_Time, 'MM/DD/YYYY')
. If your session date parameter NLS_DATE_FORMAT
is different from 'MM/DD/YYYY'
, this will result in a format error (in your case I suspect your NLS_DATE_FORMAT is something like DD-MON-YYYY
, resulting in a "month" error).
您CAST(a.S_Date AS DATE)
在查询中使用,其中a.S_Date
是 VARCHAR: To_CHAR(ITH_F.Start_Time, 'MM/DD/YYYY')
。如果您的会话日期参数NLS_DATE_FORMAT
不同于'MM/DD/YYYY'
,这将导致格式错误(在您的情况下,我怀疑您的 NLS_DATE_FORMAT 类似于DD-MON-YYYY
,从而导致“月份”错误)。
A few options:
几个选项:
- don't use
TO_CHAR
in the inner query (always try to keep the date format for internal calculations, useTO_CHAR
only where it belongs -- in the GUI). If you only want the date portion, use TRUNC. - use
TO_DATE
instead of CAST in the outer query:to_date(a.S_Date, 'MM/DD/YYYY')
, this is obviously tedious: you cast a date to a varchar that is later transformed to a date.
- 不要
TO_CHAR
在内部查询中使用(总是尝试保留内部计算的日期格式,TO_CHAR
只使用它所属的地方——在 GUI 中)。如果您只想要日期部分,请使用 TRUNC。 TO_DATE
在外部查询中使用而不是 CAST:to_date(a.S_Date, 'MM/DD/YYYY')
,这显然很乏味:您将日期转换为 varchar,然后将其转换为日期。