2171509 - Incorrect date returned when connecting to a database using an OLE DB connection in Crystal Reports

SAP Knowledge Base Article - Public

2171509 - Incorrect date returned when connecting to a database using an OLE DB connection in Crystal Reports

Symptom

  • Incorrect date displayed on the report.
  • Date are off by 100 years on some report.
  • Same report were showing the expected date before.
  • Wrong date returned in Crystal Reports, when connecting to a database using an OLE DB connection, and the registry key "NumberOfYearToShift" is set to 100.

Environment

  • Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013

Reproducing the Issue

  1. Add the Registry Key: "NumberOfYearToShift", and set the value to: 100
  2. In Crystal Reports, create a report on any data source using an OLEDB connection.
  3. Add a database field of data type: Date or Date Time to the report.
  4. When refreshing the report, notice the date displayed is incorrect, the date is off by 100 years.

Cause

  • This issue only occurs when:
    • The report uses an OLEDB connection, and
    • The registry key "NumberOfYearToShift" has been added to resolve the IBM DB2 NULL date issue, as per the SAP Knowledge Base Article 1217785
         
  • This issue has been reproduced at SAP, and has been tracked under SAP Note 2171647

Resolution

  • There is no product update that resolves this issue at this point.
       
  • To workaround the issue, use an ODBC connection, instead of using an OLEDB connection for your report. To change the connection of the report in Crystal Reports:
     
    1. In Microsoft ODBC Administrator, create an ODBC DSN to your database.
    2. In Crystal Reports, open the report based on an OLEDB connection.
    3. Under the menu "Database", select "Set Datasource Location..."
    4. In the "Set Datasource Location" window, in the "Replace with" area, double click on "Create New Connection".
    5. Under "Create New Connection", double click on ODBC, and select the ODBC DSN created in step 1.
    6. Select under "Currnet Data Source" your connection, and under "Replace with" the ODBC connection, and click "Replace"
    7. Click "Close"
       
      Now, the report is pointing to the same database, but instead of using an OLEDB connection, it is using an ODBC connection.
      Since this product defect only occurs when connecting via OLEDB, the report will now display the correct date.

Keywords

CR, wrong date, old date , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Bug Filed

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013