- Crystal Reports 2008
- Oracle database (any version)
Reproducing the Issue
- Create a new Crystal report against Oracle table with Date field.
- Create a range DateTime parameter.
- Add records selection based on parameter.
- 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.
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.
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 BusinessViews Manager , Problem