SSRS - Oracle DB, Passing Date parameter

Question

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.

Keep getting this error "ORA-01843: Not a Valid Month"

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

Answer 1

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).

A few options:

• don't use TO_CHAR in the inner query (always try to keep the date format for internal calculations, use TO_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.

Answer 2

Oracle parameters are indicated with a leading colon - @Event_Date should be :Event_Date.