1220288 - Select Expert generates the wrong date when connecting from Crystal Reports to DB2-OS390 or DB2-z/os

• Incorrect data returned
• When reporting off DB2, it returns the wrong data
• In Crystal Reports connected to DB2-OS390 or DB2-z/os, it generates an incorrect date in a record selection when using "1/1/1" as the date, therefore it is returning the incorrect data to the report.

• Crystal Reports XI
• Crystal Reports XI R2
• Crystal Reports 2008
• Crystal Reports 2011

1. In Crystal Reports, create a report off a DB2 on OS390, or Z/OS
2. Filter the report data on a date equal to 1/1/1 by creating a record selection
3. When refreshing the report, the incorrect data set is returned to the report because the Where clause of the SQL Query generate that filter the date is incorrect.

• The cause of the problem is that Crystal Reports doesn't support date formats where year value is lower than 100, due to limitations of the OLE DB Date function and the Crystal Reports engine.
• The issue has been identified and tracked under ID ADAPT00480210.

• To resolve the issue, a registry key has been added to convert the database date value to a custom date recognized by the OLE DB Date function and the Crystal Reports engine. The conversion is done when reading the database field value (date and date time) before returning to query engine. The date is converted from the custom date back to the database date before generating and executing SQL.
• To resolve the issue:
  1. Add the following registry key corresponding to the version of Crystal Reports: 
     • For Crystal Reports XI R2:
       • [HKEY_LOCAL_MACHINE\Software\Business Objects\Suite 11.5\Crystal Reports\Database\DB2CLI\CustomDateMap]
     • For Crystal Reports 2008:
       • [HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\Database\DB2CLI\CustomDateMap]
     • For Crystal Reports 2011:
       • [HKEY_LOCAL_MACHINE\SOFTWARE\SAP Business Objects\Suite XI 4.0\Crystal Reports\Database\DB2CLI\CustomDateMap]
     • NOTE: For 64bit version of MS Windows, the beginning of the path is:
       • [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\...
  2. The values of the entries indicate how the dates are translated. For example, if Registry Variable 0001/01/01 has a value of 0101/01/01, the database date of 0001/01/01 will be translated to 0101/01/01 when the data is retrieved. So, in this case, when using the Select Expert to retrieve results of the date 0001/01/01, use the value 0101/01/01.

Multiple custom date maps can be specified under the relevant key. For example, to translate year 1 to 101 and year 2 to 102, do the following:

- Give Registry Variable 0001/01/01 a value of 0101/01/01

- Give Registry Variable 0002/01/01 a value of 0102/01/01

Note that the multiple mappings of the same value can be defined. For example, to translate year values 1 and 2 to 101, do the following:

- Give Registry Variable 0001/01/01 a value of 0101/01/01.

- Give Registry Variable 0002/01/01 a value of 0101/01/01.

Note that if the Select Expert is used to retrieve entries that map 0101, the database results returned will be for the previous mapping defined for that value. For example, in this case, the year 0002 will be returned.

Known limitations: Currently, the system converts only range information (record selection) and parameter values (stored processes that use date/datetime parameters). SQL Formula fields and expression fields, which are run directly on the database server, are not converted.

CHF 480210 Crystal Reports Select Exert Date , c2019387, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed