2873861 - Error: 'Bad date format string.' when refreshing a report in Crystal Reports

• Error: Bad date format string.
• The same report refresh successfully on a different computer.
• Function: cDate, generates an error when attempting to convert a string, to a date.
• When refreshing a report that contains a formula to convert a string to a date, Crystal Reports fails with the error:
          
     "Bad date format string."
      
     

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

1. In Crystal Reports, create a report on any data source containing a date in a string format. 
           
2. Create a formula that uses the function: cDate to convert the string to a date like:
     
       cDate({String Date Field})
        
3. Add the formula to the report.
       
4. When refreshing the report, it refresh successfully.
       
5. Save the report, and when refreshing the same report on a different computer, it fails with the error: 'Bad date format string.'

• The formula fails because the string field have at least one value not in the accepted date formats, for the function: cDate, and DataValue. 
       
• The accepted date formats varies depending on the computer regional settings, therefore, the same string date value that can be converted on one computer, may generates an error on another computer that uses a different regional setting.

• To convert a string date value to a date:
• Use the isDate function first; or
• If the report will be used in different regions, and in different languages, create your own string date conversion formula.
       
          
• Use the isDate function first
  If all the computers will be using the same regional setting, then when using the function CDate or DateValue, use the IsDate function first to check if the value is in an expected date format.
    
1. In Crystal Reports, open the report that generates the error.
     
2. Edit the formula that converts the string date value to a date.
     
3. In the Formula Workshop, add the function isDate first to validate the value.
    
   For example, if the original formula was like:
        
        cDate({String Date Field})
        
   Then update the formula with the isDate function, like:
          
        If isDate({String Date Field}) Then cDate({String Date Field})
       
   If there is a date value not in the expected string date format, it will return a blank date on the report, instead of an error.
            
               

• Create your own string date conversion formula
  If the report will be used in different regions, and in different languages, then create your own string date conversion formula based on how the string date value is stored in the database.

1. On the database, confirm the format the date is stored in the string field.
     
2. In Crystal Reports, open the report that generates the error.
      
3. Edit the formula that converts the string date value to a date.
      
4. In the Formula Workshop, create your own formula to convert the string date to a date.
       
   For example, the following formula takes a string in the format YYYY/MM/DD, and convert it to a date.
       
   Local StringVar stringDate := <INSERT YOUR DATE STRING FIELD HERE>; // String field containing a date in the format YYYY/MM/DD
   Local StringVar Separator := "/";          // Character Separating the String Date.
   Local NumberVar positionYear    := 1;   // Position of the Year in the string.
   Local NumberVar positionMonth := 2;   // Position of the Month in the string.
   Local NumberVar positionDay    := 3;   // Position of the Day in the string.
         
   Local NumberVar myDay := 0;
   Local NumberVar myMonth := 0;
   Local NumberVar myYear := 0;
   Local DateVar myDate := Date(0,0,0);
     
   If Separator in stringDate Then
   (
      Local StringVar Array arrayDate := Split(stringDate,separator);
      If uBound(arrayDate) = 3 Then
      (
         myDay := If isNumeric(arrayDate[positionDay]) Then ToNumber(arrayDate[positionDay]) Else 0;
         myMonth := If isNumeric(arrayDate[positionMonth]) Then ToNumber(arrayDate[positionMonth]) Else 0;
         myYear := If isNumeric(arrayDate[positionYear]) Then ToNumber(arrayDate[positionYear]) Else 0; 
        
        If myYear > 100 Then 
            If (myMonth in [1,3,5,7,8,10,12]) and (myDay in 1 to 31) Then
                 myDate := Date(myYear,myMonth,myDay)
             Else If (myMonth in [4,6,9,11]) and (myDay in 1 to 30) Then 
                myDate := Date(myYear,myMonth,myDay)
             Else If (myMonth = 2) and (myDay in 1 to Day(Date(myYear,3,1)-1)) Then
                myDate := Date(myYear,myMonth,myDay); 
      );
   );
   myDate
      
       
   Note: 
   If you are going to convert string date to date on multiple reports, you may want to create a custom function out of the formula created. For more information on Custom Function, see the Crystal Reports Help.

CR, CR Date , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016