How to change the datatype of parameters to only date

[Former Member]

Hi,

I have used stored procedures in my crystal report. The parameters that correspond to stored procedure date comes in datetime format.

Is there a way to change the datetime format of Crystal report parameters to date only. Please let me know.

Regards,

Simran

[abhilash_kumar · Accepted solution]

Hi Simran,

Do you mean to say that the stored proc has a date prompt however, CR treats it as datetime?

What database are you reporting against?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Actually I am using Oracle database. In Oracle all the date formats have time it. When I use the stored procedure in crystal report the parameter which comes has time with it.

Regards,

Simran

[abhilash_kumar · Accepted solution]

And how has the date prompt been defined in the SP?

Is it defined as Date or Datetime?

-Abhilash

[Former Member · Accepted solution]

The datatype for date is date in Oracle but Oracle also saves time in date datatype.

Regards,

Simran

[abhilash_kumar · Accepted solution]

Workaround 1:

1) Insert a blank main Report

2) Create a 'date' prompt in this Main Report

3) Create a formula in the Main Report with this code:

CdateTime({date_prompt_in_main_report}, Time(0,0,0))

4) Insert the report that points to the SP as a subreport

5) Via the 'Change Subreport links' option, link this formula field to the Subreport's datetime prompt

Workaround 2;

Use the 'Add Command' option to call the SP. E.g:

Exec Procedure_Name to_date({?Date_prompt})

The Date Prompt needs to be created in the Command Window.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

When I am using the CDateTime function in the main report it is giving me an error that a date is required here as shown in the attached screenshot.

Can you please help.

Regards,

Simran

[abhilash_kumar · Accepted solution]

The error indicates that the 'from date' prompt it not 'date'.

-Abhilash

[Former Member · Accepted solution]

It is actually datetime.

Regards,

Simran

[abhilash_kumar · Accepted solution]

You'll need to 'create' a 'date' prompt.

Just follow 'Workaround 1' from reply at 3:42 PM.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am not fully able to follow. If I create a date type prompt in main report how can it be linked to datetime prompt in the subreport.

Also you have asked to link the formula field to subreport. Is it possible for linking the formula field to sub report prompt. Please let me know.

Regards,

Simran

[abhilash_kumar · Accepted solution]

Hi Simran,

Here are the steps again:

1) Insert a blank main Report

2) Create a 'date' prompt in this Main Report

3) Create a formula in the Main Report with this code:

CdateTime({date_prompt_in_main_report}, Time(0,0,0)) //this is a datetime formula that links to the datetime prompt of the subreport's datasource

4) Insert the report that points to the SP as a subreport

5) Via the 'Change Subreport links' option, link this formula field to the Subreport's datetime prompt

Right-click the sub-report > Select Change Subreport links > Move the formula field in the Main Report to the 'Fields to Link to' area > From the drop-down on the bottom left that says 'Subreport Parameter field to use', choose 'the datetime prompt' from the dropdown.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

When I select the formula fields in the Field(s) to link to and click on the dropdown for Subreport parameters fields to use the datetime prompt the datetime prompt does not appears in the dropdown.

Can you please tell?

Regards,

Simran

[abhilash_kumar · Accepted solution]

This indicates that the stored proc prompt is not datetime.

-Abhilash

[Former Member · Accepted solution]

The datatype present in Oracle is Date but it also stores time with it.

How should I handle this?

Regards,

Simran

[abhilash_kumar · Accepted solution]

When you connect to the stored proc from CR, does the calendar prompt include a timestamp too?

-Abhilash

[Former Member · Accepted solution]

Yes the calendar prompt includes a timestamp.

Regards,

Simran

[former_member617021]

After spending countless hours on this, I have come up with a solution... I've tested this against an Oracle stored procedure, but should work for MS SQL Server as well.

Basically you need to trick Crystal Reports into treating the parameter as a date.

Here are the abbreviate instructions I've written and tested against Crystal Reports 2016 Support Pack 7, version 14.2.7.3069:

1. Create a blank report.
2. Add Command
3. Create Parameter(s), use SAME NAME as the stored procedure’s date parameter
4. Set SQL query to “select {?<parameter name>} <parameter name> from dual”, where <parameter name> is the name of the parameter you just created (e.g. “select {?STARTDATE} STARTDATE, {?ENDDATE} ENDDATE from dual”).
5. Add Stored Procedure
6. In the Field Explorer, expand Database Fields and then expand Command.
7. Drag the field returned by the Command to one of the bands of the report (e.g. Page Header).
8. If there are multiple fields being returned by the Command (e.g. if you have multiple date parameters you are spoofing), you should only have to drag one of the fields from the Command to the report band.
9. If you do not want to see this field in the report, format field and set “Suppress” to true.
10. Save, Close, and Re-Open report.
11. In the Field Explorer, expand Parameter Fields and verify the parameter Type is still “Date”.

Detailed Instructions:

1. Create a blank report.
2. Open the Database Expert (i.e. from the main menu: Database -> Database Expert…
3. In the Database Expert, expand the data source you will use for your stored procedure.
4. Double-Click on “Add Command”.
5. Click “Create…” to create a new Command Parameter.
6. Set Parameter Name to the SAME name of the date parameter used by the stored procedure.
7. Set Prompting Text (optional).
8. Set Value Type to “Date”.
9. Set Default Value (optional).
10. Click OK to finish creating the parameter.
11. Set SQL query to “select {?<parameter name>} <parameter name> from dual”, where <parameter name> is the name of the parameter you just created (e.g. “select {?STARTDATE} STARTDATE from dual”).
12. If there are multiple date parameters to the stored procedure you need to spoof, repeat steps 5-10 for each parameter, and (in step 11) return each parameter as a column in the select statement.
13. Click OK to close the Add Command To Report dialog.
14. The “Enter Values” dialog should pop up; select a valid date and click OK.
15. (at this point, you should be back to the Database Expert dialog)
16. Under the data source you selected earlier, expand the owner of the stored procedure and find the stored procedure you want to use for the source of the report and double-click on the stored procedure to add it to the Selected Tables.
17. When the Enter Values dialog pops up, you’ll notice it should be asking you for a date (not date/time) for the date parameter you defined; enter valid values for the date and any other parameters, then click OK.
18. (at this point, you should be back to the Database Expert dialog)
19. Click OK on the Database Expert dialog; this won’t close the dialog, but it will change tabs from “Data” to “Links” in the Database Expert dialog.
20. If the “Clear Links” button is enabled, click it.
21. Click OK to close the Database Expert.
22. You should see a message that “Your current link configuration contains multiple starting points. Please be advised that this is generally not supported.”… click OK to dismiss the dialog.
23. You may see a message that “A stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed.”… click OK to dismiss the dialog.
24. In the Field Explorer, expand Database Fields and then expand Command.
25. Drag the field returned by the Command to one of the bands of the report (e.g. Page Header).
26. If there are multiple fields being returned by the Command (e.g. if you have multiple date parameters you are spoofing), you should only have to drag one of the fields from the Command to the report band.
27. If you do not want to
28. In the Field Explorer, expand the Parameter Fields.
29. Double-click on the date parameter you created
30. à you should see the Type of the parameter is Date (not DateTime).
31. Save and Close Report
32. Re-open Report
33. à verify the Type of the parameter is still Date (not DateTime)
34. Until we’ve had a chance to gain confidence in this approach, I recommend re-verifying the parameter Type after you drag all stored procedure fields (you plan on using) to the report and run it for the first time before you spend a lot of time formatting the report. After running the report (with all the stored procedure fields), save & close the report, re-open it and verify the parameter Type is still “Date”. After you re-open and check the parameter Type, you can proceed to format the report, etc.