Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle Date format exception in SQL Server Reporting Services

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?
like image 703
Aftab Ansari Avatar asked Mar 13 '19 14:03

Aftab Ansari


People also ask

How do I change date format in reporting services?

Format the Date You can format it to display only the date. Select the Design tab. Right-click the cell with the [Date] field expression and then select Text Box Properties. Select Number, and then in the Category field, select Date.

How do I display a date in YYYY MM DD format in Oracle?

Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.

How do I change the default date format in Oracle?

Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus: alter session set NLS_DATE_FORMAT='<my_format>'; The change is only valid for the current sqlplus session.

What is the default format for dates in Oracle database?

Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY.


1 Answers

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

like image 171
David Browne - Microsoft Avatar answered Oct 04 '22 21:10

David Browne - Microsoft