SAP Knowledge Base Article - Public

1517132 - Time stamp included in the input parameter in Crystal Reports, despite the parameter has been defined as a Date in the Oracle Stored Procedure

Symptom

  • Date Time prompt instead of an expected Date prompt.
  • Report created off an Oracle Stored Procedure with Date as an input parameter, prompt for a time stamp when the report is refreshed in Crystal Reports.
  • Is it possible to remove or avoid the time stamp for a Date Parameter?

Environment

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

Reproducing the Issue

  1. In Oracle, create a stored procedure that has an input parameter as Date data type.  (Date without the time stamp )
  2. In Crystal Reports, create a report based on the Oracle Stored Procedure, using the Oracle Native connection.
  3. When refreshing the report, it prompts for a date time parameter, why doesn't it prompt for a date prompt instead? 

Cause

  • It is normal behavior because in Oracle, the data type: Date, is defined as a field containing date and time, therefore this is why it prompts for a date time parameter, as this information given to Crystal Reports from Oracle.

Resolution

  • The following are Workarounds to not be prompted by a date time parameter when reporting off a stored procedure with an input date parameter:
     
    • Set a default value for the date parameter with a time at midnight; or
    • Report off a Command Object that calls the stored procedure; or
    • Use an ODBC connection; or
    • Update the stored procedure to use a string parameter. 
          
         
  • Set a default value for the parameter with a time at midnight
       
    To do so, open the report in Crystal Reports designer, and in the Edit Parameter dialogue box, set some default date value along with a time portion as 00:00:00. This will ensure the time portion is always 00 and only the Date value will take effect for the record selection.
                  
      
  • Report off a Command Object that calls the stored procedure
        
    In Crystal Reports, create a new report using a Command Object, and call the Oracle Stored Procedure within the Command Object, like:
      
         Call <YOUR STORED PROCEDURE NAME> (TO_DATE(TO_CHAR({?DateParam},'yyyy/dd/mm'),'yyyy/dd/mm'))
        
    Note: You will need to create a parameter of data type: date, within the Command Object, and pass it to the Stored Procedure.
         
       
  • Use an ODBC connection
      
    Use an Oracle ODBC driver that send to Crystal Reports a date data type, instead of the Oracle Native connection.
           
           
  • Update the stored procedure to use a string parameter
       
    Modify the Stored Procedure, and convert the Date parameter to a String parameter.

Keywords

ORACLE STORED PROCEDUERE, SP, CRYSTAL REPORTS, TIMESTAMP, TIME STAMP, DATE. , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

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