1437549 - Date range parameter filter doesn't pick the correct records from Oracle database

Some records of the Start Date are missing in Crystal Reports when report records selection based on range date parameter.

• Crystal Reports 2008 
• Oracle database (any version)

1. Create a new Crystal report against Oracle table with Date field.
2. Create a range DateTime parameter.
3. Add records selection based on parameter.
4. Refresh the report and select dates for the range parameter.

When DateTime parameter promts to select a Date from the calendar, it takes current time for the Time component. If parameter entered at any time other than midnight(00:00:00) then there is a time component for the Start date. As the result records for the Start date are missing.

Example:

Start Date for the report is a January 1st, 2008 and parameter entered at 10:05 am. As the result Crystal Reports will send  2008/01/01 10:05:00 to the database and all records for January 1st will be missing because 2008/01/01 10:05:00 is greater than  2008/01/01 00:00:00.

Additional information:

The Date values in Oracle database are always stored in DateTime format. There are 7 bytes in the Oracle database to store Date where:

• 1st Byte: Stores the century value but before storing it add 100 to it.
• 2nd Byte: Stores the year and 100 is added to it before storing.
• 3rd Byte: Stores the Month.
• 4rth Byte: Stores the Day of the month.
• 5th Byte: Stores the hours but add 1 before storing it.
• 6th Byte: Stores the minutes but add 1 before storing it.

• 7th Byte: Stores the seconds but add 1 before storing it. 

The default Time component for the Date value is 00:00:00.

  

Create a Date parameter in Crystal Reports Designer and manually edit Records selection formula.

(Filter Wizard would not prompt for Date parameter if the field is DateTime) 

Please note: It is not possible to change the parameter type for stored procedure connection. For Oracle stored procedure the workaround is to create a default value for the parameter with "00:00:00" time component.

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

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011