Data Issue with Oracle NVL() and SSRS/Visual Studio 2015

Question

I have an SSRS report that has the option of running for two date ranges.

If it's run adhoc from the Report Manager in a web browser, the user can specify a start date and stop date in the date drop downs.

For Report subscriptions, there are hidden parameters that specifies what the dates should be depending on the parameter. This can be today's date, last week, etc.

In the report data query we use:

between nvl(:i_start_date,:subscription_start_date) and nvl(:i_stop_date,:subscription_stop_date)

This worked in SSRS 2012 and Visual Studio 2010. However, I am receiving the following error in SSRS 2016 and Visual Studio 2015:

ORA-01830: date format picture ends before converting entire input string

If we remove the nvl(), or use a non-parameterized value in nvl, the problem does not occur. For example:

and cr.contact_date between :i_start_date and :i_stop_date -- works, but only for report manager web runs
and cr.contact_date between :subscription_start_date and :subscription_stop_date -- works, but only for subscriptions
and cr.contact_date between nvl(:i_start_date,sysdate) and nvl(:i_stop_date,sysdate) -- works, but obviously not using the parameter date.

Why does using nvl() cause this date picture error? It's as if the Date/Time type is not passed into the Oracle query properly from SSRS.

All date parameters are set to Date/Time and the subscription parameters return a value that has been converted to date using CDate().

Here's the code for the subscription start parameter. The subscription stop is similar:

=CDate(switch(
Parameters!SubscriptionParam.Value=0, DateSerial(1990,1,1) ,
Parameters!SubscriptionParam.Value=1,today(),
Parameters!SubscriptionParam.Value=2,dateadd("d",-1,today()),
Parameters!SubscriptionParam.Value=3,dateadd("d",-7,today()),
Parameters!SubscriptionParam.Value=4,DateSerial(Year(today()), Month(today()) - 1, 1)
))

0 = Free, 1 = Yesterday, 2 = Today, 3 = Last Week, 4 = Last Month. The dates returned are correct.

Eneerge Eneerge · Accepted Answer · 2017-03-17T22:03:16

I could not find anyway to make the previous code work so I converted the code to the following:

and (
  (:SubscriptionParam = 0 and cr.contact_date between nvl(:i_start_date,'01-JAN-1900') and nvl(:i_stop_date, sysdate))
  or
  (:SubscriptionParam <> 0 and cr.contact_date between nvl(:subscription_start_date, '01-JAN-1900') and nvl(:subscription_stop_date, sysdate))
)

Data Issue with Oracle NVL() and SSRS/Visual Studio 2015

1 Answers