Passing date parameters in Crystal 9 1

[starbase59]

We are passing a StartDate and EndDate as a Datetime datatype from SQL 2000. The time is formatted as 00:00:00, but Crystal requires the time parameter formatted as 12:00:00. The work around in the stored procedure is

convert (varchar,@EndDate,101)

but this is going to be a pain.

Is there someway to get around putting in the time parameter in Crystal 9? Can we reformat it?

 

[oneilltnc]

If you are passing a DateTime from SQL Server and you want to be able to format it within Crystal right click on your parameters in the field explorer and choose edit. Drop down the value type combo box and select Date instead of DateTime. This should take away your 00:00:00 time issue.

 

[starbase59]

It's not possible to delete the time parameter in the edit parameter fields. We tried that.

 

[starbase59]

The solution is to insert a subreport into a main report. You can then edit the data passed to the parameters to just date, not date and time. Works well, but subreport needs some format tweeking.

 

[rnd]

In CR 9 they took away the blanket conversion of 'DateTime' to 'Date' type through Report Options. However you can achieve the intended result indirectly.
1. Create a formula (DateFormula1) for the Date Field you want to use converting the DateTime field to Date. Your formula should look like this
CDATE(Table1.DateTimeField1)
2. Create a Parameter (DateParam1) with a value type 'Date'.
3. Go to Select Expert. Click on New - Select the formula (Date1) - select the operator you want - select the parameter (DateParam1) (this should be the only value you can see in the right side pull down menu/list box) - Click on Ok to save.
This should work. You have to painfully repeat the process for multiple date parameters or data range parameters.
This is the basic idea. You can apply this to your situation and needs.
Good Luck.

 

[purpleanne]

Does that mean that if I want the report to represent a range of dates, I need to either make the start date and end date separate or have the selection be a formula referring to minimum and maximum of the range?

I tried on a report I'm working on to do what you suggested with a range. The report has a couple of subreports which use the same parameter (represent the same dates). The container report seemed to work with just {mydate} = {?DateParms} but the subreports did not work with {mydate} = {?Pm-?DateParms} - came up empty.

Is there a way less messy than having a start date parameter and an end date parameter?