1767180 - Incorrect data returned in Crystal Reports when using the "IN" operator on a date range

• Incorrect data set returned.
• When the start date is earlier than the end date, the report returns data.
• In Crystal Reports, when using the "IN" and "TO" operator to filter data, and the start date parameter value is greater than the end date parameter value, it returns data, despite the expectation is to have no data.

• Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
    
• SAP Crystal Reports, developer version for Microsoft Visual Studio
• Microsoft Visual Studio 2010 / 2012

1. In Crystal Reports, create a report off any data source.
2. Create 2 date parameters and name it like: "Start Date" and "End Date".
3. Create a Record Selection Formula to filter the dates based on the values entered in the date parameters like:

                   {Database Date} in {?Start Date} to {?End Date}

4. Refresh the report, and when prompted by the parameters, set  the start date parameter value greater than the end date parameter value.
5. Notice the SQL Query generated is incorrect, and the report returns the incorrect data set.

For example: If the Start Date is March 20,  and the End Date is March 9.

Then the SQL Query generated looks like:

• Select MyField1, MyField2
• From   MyTable
• Where  ( DATE > {ts '2012-03-09 00:00:01'} and  DATE<= {ts '2012-03-20 00:00:00'})

 And the reports returns data, despite the logic should be that it doesn’t returns any data.

• When using the operator IN and TO in Crystal Reports, it is interpreted as a range of values, and the lowest value will always be used for the start of a range for all data type: String, Number, Time and Date. Therefore it is normal that the report returns data if there is data within that range.
• But there was an issue that have been identified where the beginning of the date range isn't included, and therefore not returning the expected result set. This issue has been tracked under ADAPT01645563.

• The lowest date value is now included in the date range after applying the following product updates:
    
• SAP Crystal Reports 2008:
  • Fix Pack 4.3
  • Fix Pack 5.3
  • Service Pack 6
      
• SAP Crystal Reports 2011:
• Service Pack 7
    
• SAP Crystal Reports 2013
• Service Pack 2
    
• SAP Crystal Reports, developer version for Microsoft Visual Studio
• Service Pack 7
    
• Without applying any product update, to generate the correct SQL Query, modify the Record Selection to use the greater than and less than operator, instead of the IN and TO operator like:
    
               {Database Date} >= {?Start Date} and {Database Date} <=  {?End Date}
    
  This way, it will returns no data when the start of the date range, is greater than the end of the date range.

CR, 1645563 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2011, feature pack 03 ; SAP Crystal Reports 2013