Oracle Date format exception in SQL Server Reporting Services

Question

Earlier my client was using SSRS 2008R2 with Oracle as transaction database. Recently they have upgraded to SSRS 2017 and now many reports are throwing following error:

ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: [AbnormalTermination:ReportProcessing], Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'Ds_Main'. ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-01830: date format picture ends before converting entire input string

After closely looking into report query, I have noticed that this error is for all those reports where oracle function TO_DATE(<Date Value>) has been used without date format. For example:

To_date(:Date_Parameter) -> this syntax throws above mentioned error
To_Date(:Date_Parameter,’MM/DD/YYYY’) -> this syntax works perfectly

I am willing to know:

1. what has changed in SSRS 2017 vs SSRS 2008 R2 that is causing this issue because same reports are working as expected in SSRS 2008 R2 and it is throwing above error in SSRS 2017.
2. Is there is any suggestions to fix this issue without updating bunch of reports?

Gravure

Do the reports all call a stored procedure where you could update the format of the date prior to passing to the report? Or do you have the query set within each report? From what I can see, you may have some data that doesn't fit the default date formatting. Ideally, you wouldn't have relied on implicit conversion at such a wide level. – Bryan

All reports are using embedded Oracle plsql query within report only – Gravure

@Aftab in the question you mentioned Any suggestion to fix this issue without updating bunch of reports, after reading your comments on the answers below it looks like the question is changed to I am willing to know what has changed in SSRS2017 vs SSRS2008R2 that is causing this issue because same reports are working as expected in SSRS2008R2. please update your question to be more specific what you are looking for. Since i don't think there are more workarounds than mentioned in the answers. – Theobald

@Aftab i think yahfoufi meant that you should add the new question not remove the old one. Since you cannot change a question after receiving answers. I fixed it – Pallette

@Aftab i though it was very hard to decide which answer to accept. Since you got 2 helpful answers and each answers target a part of your question. At the end one user will get the bounty. Good luck – Theobald

@Theobald 24, i don't see an option to award bounty? – Gravure

Answer 1

what has changed in SSRS2017 vs SSRS2008R2

SSRS 2008 used the old System.Data.OracleClient. In SSRS 2016 and later you have to install the Oracle ODP.NET provider, built and supported by Oracle. So probably just a difference in how the NLS_DATE_FORMAT session parameter is set by the two drivers.

You can see your setting if you add a dataset to your report with this query:

select parameter, value 
from nls_session_parameters 
where parameter like 'NLS%'
order by parameter

Unfortunately there doesn't appear to be a way to globally change the client date format in Oracle.ManagedDataAccess, so you'll have to make all the changes in the report dataset queries.

Alternatively you can try to ensure that you are passing Date parameters and not string parameters. If you pass a date to Oracle's to_date() function, you don't need to specify a format.

The docs for SSRS 2014 "This built-in data source type is based on the .NET Framework Managed Provider for Oracle and requires an Oracle client software component."

And for SSRS 2016 "This built-in data source type uses the Oracle Data Provider directly and requires an Oracle client software component."

Moonwort

Thank you David for highlighting the architectural change here. Does this change mentioned in SSRS2017 MSDN? – Gravure

Answer 2

Trying to figure out the issue

I don't think the issue is related to the Visual Studio upgrade. It is related to the date format passed as a parameter to TO_DATE() function.

Based on the official documentation of the Oracle / PLSQL: ORA-01830 Error Message:

Cause: You tried to enter a date value, but the date entered did not match the date format.

In Oracle, the default date format is generally DD-MON-YYYY. If you try to enter a date value that does not comply with this format.

You seem to have passed the date parameters in the dd-MMM-yyyy format and now they are passed as MM/dd/yyyy.

First of all, check that the regional setting or the applications Culture information didn't change.

---

Possible workarounds

You can fix the issue using several approaches:

(1) Handling the parameters date format

If you do not want to edit all code, then it is easier to force the parameter data string format, make sure that all parameters passed to TO_DATE() function are in following format (or try to change the default date format from the OS regional settings):

dd-MMM-yyyy   example: 01-AUG-2019

(2) Adding the date format to TO_DATE function

If you are sure that the date parameters format is fixed and will not change then you can edit your code as you mentioned in the question:

To_Date(:Date_Parameter,’MM/DD/YYYY’)

(3) Pass the date and format as parameters

This also requires changing the code, but you will pass the date and format as parameters.

To_Date(:Date_Parameter,:DateFormat_Parameter)

• SQLFiddle demo for parsing dates

You may find other methods at the following link:

• ORA-01830: date format picture ends before converting entire input string / Select sum where date query

---

Update 1 - Making common change in multiple reports

While searching I found the following link providing a method to loop over reports and make changes. You have to only replace To_Date(:Date_Parameter) with To_Date(:Date_Parameter,’MM/DD/YYYY’):

• SSRS - Make common change in multiple reports in one click

---

Update 2 - Other possible workarounds

Forcing Culture info by editing ReportViewer.aspx

You can edit the ReportViewer.aspx file is located in the SQL Server reporting services directory, and force the culture info used within reports. Check out the following question it will give you more details:

• I want Datetime Parameter in DDMMYYYY Format in ssrs report

Changing the browser language settings

Check the following link (read Mike Honey and Nick St Mags answers):

• SSRS Datetime Parameter value should display in DD/MM/YYYY format

---

Update 3 - Issue cause

In addition of what @DavidBrownie posted i found the SQL Server 2008 R2 documentation:

• Oracle Connection Type (SSRS 2008 R2)

Where they mentioned:

This built-in data source type is based on the .NET Framework Managed Provider for Oracle and requires an Oracle client software component.

Also if you take a look at SQL Server 2017 documentation:

• Oracle Connection Type (SSRS 2017)

This built-in data source type uses the Oracle Data Provider directly and requires an Oracle client software component.

In addition, referring to Microsoft OLE DB Provider for Oracle documentation (which is the old used provider). They mentioned that:

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use Oracle's OLE DB provider.

Which is the reason for changing the provider used to connect to Oracle in Reporting Services.

Pallette

@Aftab Try changing the OS regional settings as mentioned in the first approach – Pallette

@Aftab how parameters are passed to the report?? – Pallette

Thank you Hadi for your suggestion. I understand that by adding required date format will resolve this issue. I am willing to know what has changed in SSRS2017 vs SSRS2008R2 that is causing this issue because same reports are working as expected in SSRS2008R2. Since there are more than 500 Reports that has similar issue, I am willing to find a way that could help me to go ahead without report code modification. – Gravure

I can’t change OS regional settings as it will impact other application. All reports are using To_Date(<Report Date Parameter>.value) method and its date/time report data type – Gravure

Hi @Pallette – Thanks for the link but I am willing to know the architectural/behavior change in SSRS2017 vs SSRS2018 that is causing this issue instead of going for report modification. – Gravure

@Aftab i found the official documentation of SSRS 2008R2 check the answer update. Now, if you combined my answer with David Brownie answer you have all informations you need. – Pallette

@Aftab you are always welcomed. I fully respect your choice. One kindly note: please do not change the main question after having answers. You started asking for workarounds then you changed the main question to ask about the cause. Anyway hope that i helped solving the issue. Good luck – Pallette