SQL Server / Crystal Issue

[shaunk]

I'm not sure if this is a Crystal or SQL Server issue but I have approached the problem from both ends to no avail.

I am running Crystal reports that use SQL Server stored procedures as the data source.
I send the date parameters as a string from Crystal, and do a conversion on them in the procedure to datetime.
e.g. CONVERT(DATETIME, @START_DATE)
This is because I don't want the users to have to enter the time component of the date parameter. This all works OK.

The problem I have is when I run a main report that runs subreports that are a mixture of using stored procedures and direct Crystal access to the D/B. Of course, I wish to link to the subreports using only one set of date parameters.
So to link to the reports using Crystal direct access, the parameters have to be setup as type 'date'. However, I can't use this approach for the reports using stored procedures because there is no such data type as 'date' in SQL Server.
I tried to get around the problem by sending the date from the main report as type 'string'....fine for the subreports using stored procedures, but not possible for the others as there is no opportunity to 'cast' the string to a date for the other subreports before thay are run.

I hope I explained this well. At the end of the day, I may have to settle for using two sets of reports..one using stored procedures as access and the other direct Crystal access.

 

[Bond7]

What version of crystal are you using?

 

[donutman]

Version 7 CR? Yah, best to keep it consistent. Maybe a kludge is to have the user enter 2 parameters: one as date and the other as string.
Is it unreasonable to recompile the reports and always use a string? If you don't change the names of the SPs, then it doesn't require a complete redesign.
-Karl

 

[shaunk]

The version of Crystal is 8.0

 

[vidru]

I'd go ahead and change the procedure parameters to DateTime, and tell your users to ignore the time part. Then, handle the time in the procedure something like this:
[tt]
/* Set start and end dates to midnight */
SET @StartDate = CONVERT(DATETIME,CONVERT(CHAR(10), @StartDate, 101))
SET @EndDate = CONVERT(DATETIME,CONVERT(CHAR(10), @EndDate, 101))
/* Set the end date to the last second of the day */
SET @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))
[/tt]
If that's not workable for you, just create two formulas on the main report that convert the string "Date" parameters to Date or DateTime (using CDate or CDateTime), and link the subreport's date parameters to your formulas.

-dave

 

[shaunk]

Dave,

The first part is workable, I'm more or less doing that now, but does not apply in this case, as the subreports that do not use the stored procedure will accept the parameters as is (with the user ignoring the time component) and return incorrect results.

I'll have a go at your second suggestion..sounds definitely workable..thanks

 

[Bond7]

create formula to covert the string into date using CDate function. Use this date formula to link Main report to the subreports.