1517132 - Parameter prompt for a Date and Time when reporting of an Oracle Stored Procedure with a Date Parameter in Crystal Reports

• Date Time prompt instead of an expected Date prompt.
• Report created of 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?

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

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? 

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

• The following are Workarounds to not be prompted by a date time parameter when reporting of a stored procedure with an input date parameter:
   
• Set a default value for the date parameter with a time at midnight; or
• Insert the report based of the stored procedure as a subreport; or
• Report of 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.
                
   
• Insert the report based of the stored procedure as a subreport
  
  For details on how to use this technique, see the SAP Knowledge Base Article: 1652217
  
  
  
• 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.

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

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