1217785 - IBM DB2 date fields appear with a default null value of 01/01/2001 in Crystal Reports

• Blank date appears as 01/01/2001.
• Null date displays in Crystal Reports as 01/01/2001 when connecting to an IBM DB2 data source.
• Null date in IBM DB2 are stored as 01/01/0001, but appears as 01/01/2001 in Crystal Reports when connecting to IBM DB2 via an ODBC connection. 

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

1. In Crystal Reports, create a report off an IBM DB2 database via an ODBC connection.
2. Add a table that has a date field where there is null date.
3. Insert the date field on the report.
4. Preview the report.

     Notice the IBM DB2 null date, which is stored as 01/01/0001 in DB2, appears as 01/01/2001 in Crystal Reports.

• This issue was tracked under Problem Report ADAPT00175452
      
• The cause of this issue is due to the date structure in Crystal Reports having a lower boundary value of January 01, 100 (01/01/0100), therefore any date value lower than the boundary will have 2000 added to it. For example in DB2, the default null value for a date is 01/01/0001, so it will appear as 01/01/2001 in Crystal Reports.

• For DB2 null date to show as: 01/01/0001 in Crystal Reports, add the registry key: NumberOfYearToShift, and set the value to: 100
    
             
  WARNING The following resolution involves editing the Microsoft Registry. Using the MS Registry Editor incorrectly can cause serious problems. Use the MS Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322
        
        
1. Open the Microsoft Registry Editor.
   ( In MS Windows, under the menu Start, select Run, and type: regedit )
        
        
2. In the Microsoft Registry Editor, navigate to the path corresponding to the version of the product used:
       
• Crystal Reports 2008   
      
• For 32bit version of MS Windows:
  HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\Database 
      
• For 64bit version of MS Windows:
  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\Database 
          
        
• Crystal Reports 2011, 2013, 2016   
      
• For 32bit version of MS Windows:
  HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database     
      
• For 64bit version of MS Windows:
  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database 
          
         
• SAP Crystal Reports, developer version for Microsoft Visual Studio
     
• HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\Database 
      
            
• RAS 2011   
      
• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database  
    
          
3. Add the String Value: NumberOfYearToShift
           
   Important Note: The registry key must be a String value. If it's set to a DWord, then all of the dates in Crystal Reports will be increased by 100 years.
                  
      
4. Set the value of NumberOfYearToShift to: 100
        
         
• After performing the above steps, when refreshing the report in Crystal Reports, the null date will appear has 01/01/0001
       
  NumberOfYearToShift will increased the year by 100 to reach the lower boundary limit for the date, then convert the date, and finally subtract 100 years to return the correct date of 01/01/0001 in Crystal Reports.
          
       
                       
• SDK Issue
  A report will compare against the date correctly, as long as in the Designer you use Report->Selection Formula->Record and not Report->Select Expert->Record. When the Select Expert reads the formula written using the Selection Formula, it misreads it. The RAS SDK misreads it in a similar fashion.
     
  For this example, here’s the text actually stored in the report as a Selection Formula:
  
       {table.fieldname} = Date (1, 1, 1)
    
  Any API that tries to read this will misinterpret it.
     
        rcd.getDataDefController().getDataDefinition().getRecordFilter().getFilterItems().getFilterItem(i).computeText();
        
  returns the formula used, but it interprets the date and changes it to 2001.
     
  This call will interpret the filter and let you decide how to interpret it, but it also changes it to 2001:
             rcd.getDataDefController().getDataDefinition().getRecordFilter().getFilterItems().getFilterItem(i).displayText(FieldDisplayNameType.description, Locale.US);
      
  Here are the results of the different types that can be chosen:
     
• Selection formula  :  ({table.fieldname} = Date(2001, 1, 1))
• as description        : (CrystalFieldName = 1/1/01)
• as field name        : (fieldname = 1/1/01)
• as formula name   : ({table.fieldname} = Date(2001, 1, 1))
• as heading text     : (CrystalColumnHeader = 1/1/01)
• as long name       :  (table.fieldname = 1/1/01)
• as short name      :  (fieldname = 1/1/01)

CR, IBM, DB2, DB2 date field default value, NULL Date, Empty Date , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

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