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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 20:49:01  来源:igfitidea点击:

SSRS - Oracle DB, Passing Date parameter

oraclereporting-services

提问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_Dateis a VARCHAR: To_CHAR(ITH_F.Start_Time, 'MM/DD/YYYY'). If your session date parameter NLS_DATE_FORMATis 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_CHARin the inner query (always try to keep the date format for internal calculations, use TO_CHARonly where it belongs -- in the GUI). If you only want the date portion, use TRUNC.
  • use TO_DATEinstead 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,然后将其转换为日期。