1976422 - Error: ‘HY000:MALFORMED_QUERY' when refreshing a report based off Salesforce.com, that filter data on a date, in Crystal Reports

• When refreshing a report in Crystal Reports 2013, it fails with the error: "Failed to retrieve data from the database".
   
• Used to be able to refresh the same report in Crystal Reports 2011.
     

• When reporting off Salesforce.com in Crystal Reports 2013, if the report contains a Record Selection Formula that filter on date fields, it fails with the following errors:
    
  "SAP Crystal Reports: Failed to retrieve data from the database"

  "SAP Crystal Reports: Database Connector Error: ‘HY000:MALFORMED_QUERY: CreatedDate >= YYYY-MM-DD 00:00:00 ) AND ( CreatedDate < YYYY-MM-DD Error at Row:1:Column:81  line 1:81 no viable alternative at character ‘ [Database Vendor Code: 70]’ "
       

• Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.  

• SAP Crystal Reports 2013
     
• Salesforce.com

1. In Crystal Reports 2013, connect to a Salesforce.com data source.
2. Add a Salesforce.com table that contains a date field to the report like the "Account" table.
3. Create a Record Selection Formula to filter the data on a date database fields. The selection formula may look like:
      
   {Account.CreatedDate} = Date(2014,01,01)
    

4. Refresh the report, and it will fails with the following error messages:

   "SAP Crystal Reports: Failed to retrieve data from the database"
   
   

   "SAP Crystal Reports: Database Connector Error: ‘HY000:MALFORMED_QUERY: CreatedDate >= 2014-01-01 00:00:00 ) AND ( CreatedDate < 2014-01-02 Error at Row:1:Column:91 line 1:91 no viable alternative at character ‘ [Database Vendor Code: 70]’ "

   
      
       
   
      

• The issue has been identified and logged under Problem Report ID ADAPT01718495

• The issue is resolved in the following product updates:
           
• SAP Crystal Reports 2013:
• Support Pack 02 - Patch 4
• Support Pack 03 - Patch 1
• Support Pack 04
       
• If the product update cannot be applied for some time, then to workaround this situation, move the date filtering from the "Record Selection Formula" to the "Saved Data Selection Formula":
    
1. Open the report in Crystal Reports.
     
2. Remove the date filtering from the Record Selection Formula.
     
3. Under the menu "Report", select "Selection Formulas - Saved Data..."
     
4. Enter the filtering for the date in that formula, like:
      
       {AccountHistory.CreatedDate} = Date(2014,01,01)
        
5. Save and close the formula.
      
   Now, when refreshing the report, it will returns all the data filtered by the Record Selection Formula, and then filter the data set returned to Crystal Reports based on the date filtering entered in the "Saved Data Selection Formula".
          
   Note that this potential workaround can affect the report performance if there is a large data set returned, because the filtering of the data will be done on your local machine, instead of performed on the Salesforce.com cloud.
   
   

CR, Salesforce, SForce, SP02, SP03, SP04, Salesforce.com, ADAPT01718495,  ADAPT01718496, ADAPT01719327, ADAPT01719328, date field, filter data on a date, filter by date, failed to retrieve data from the database, HY000, MALFORMED_QUERY, CreatedDate , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed