CODEWITHSUNDEEP
oracle-databasereporting-services
Eneerge
Eneerge

Reputation: 81

SSRS 2016 Oracle Date Casting Fails

After upgrading from SSRS 2012 to 2016, we've had to rewrite all of our reports because of an issue with SSRS giving ORA-01830: date format picture ends before converting entire input string.

The code that causes the issue is below:

WHERE   (
  trunc(date_processed) BETWEEN NVL(:start_date,:subscription_start_date) AND NVL(:end_date,:subscription_end_date)
)

Start/End_date are both null at the beginning of the report execution. subscription_start/end_date is NEVER null and is always set. To make things even more frustrating, the following works fine:

WHERE   (
  trunc(date_processed) BETWEEN  NVL(:start_date ,'01-JAN-1848') AND NVL(:end_date,'31-DEC-2039')
  and trunc(date_processed) BETWEEN :subscription_start_date and :subscription_end_date
)

The issue, however, is that now the :start_date parameter can not override the subscription date parameter when it is set by the user.

This did not occur on previous versions. This is happening to ALL reports we have which isn't a few.

Upvotes: 0

Views: 756

Answers (2)

CltGeek
CltGeek

Reputation: 1

We were able to get around this issue by using an expression to format the date parameters on the output Dataset Parameter Properties with the following formulas.

=Format(Parameters!BeginDate.Value, "dd/MMM/yyyy")
=Format(Parameters!EndDate.Value, "dd/MMM/yyyy")

Upvotes: 0

user3096487
user3096487

Reputation: 399

Setting the variable parameters in SSRS to text and using TO_DATE resolved similar situations for me using SSRS with an Oracle source.

WHERE ( trunc(date_processed) BETWEEN NVL(TO_DATE(:start_date,'mm/dd/yyyy'),TO_DATE(:subscription_‌start_date,'mm/dd/yy‌​yy')) AND NVL(TO_DATE(:end_date,'mm/dd/yyyy'),TO_DATE(:subscription_en‌​d_date, 'mm/dd/yyyy') )

Upvotes: 0

Related Questions