Reporting off datetime stored procedure

[Luvsql]

I have created a stored procedure within MS SQL which contains a @STARTDATE and @ENDDATE. Within the SP, there are < <= and >= in the select statements.

When I EXEC this SP within Query Analzer, I get amount x, but when it is run within Crystal Reports 8.5, using the same dates, I get different amounts. I have verified that the data that QA shows is correct. I believe Crystal may not be taking the beginning and end dates into consideration when crystal passes that query. Why would the same SP yield different data?

 

[synapsevampire]

Crystal doesn't pass a query if it's using an SP.

I'd be more suspicious of the connectivity that you're using, meaning different data sources, or those not supported by Crystal (for instance you should use the CR supplied ODBC driver).

-k

 

[FVTrainer]

One other thing that is easy to overlook is taking into account the time portion of your datetime parms. When you say you are using the same dates in Crystal as you are in QA, are you certain that the time portion is also the same?

Here's another thought, particularly if your data actually uses the time portion of the datetime field you're using in the WHERE clause of the stored proc (while some might say this qualifies more as a workaround, it would give you a way of completely controlling how the time portion is handled): Change your stored proc to take varchars as the value type for two &quot;date&quot; parameters and then convert the entries to datetime in the stored proc, supplying the necessary time portion. Use the result in your WHERE clause. I know a few SQL dba's who insist on this approach and don't view it as a workaround but rather as a control point.

 

[Luvsql]

When I pass the dates in QA, I simply have to:

EXEC SP @STARTDATE='2003-05-05', @ENDDATE='2003-05-09'

I don't need to enter a time parameter using SQL QA, but Crystal seems to require it.

 

[vidru]

Exactly what I was thinking FVT.

Luv, this is how I handle this exact situation in all of my procedures:

-- Get rid of the time portion
SET @StartDate = CONVERT(SMALLDATETIME(CHAR(10), @StartDate, 101))
SET @EndDate = CONVERT(SMALLDATETIME(CHAR(10), @EndDate, 101))
-- Set @EndDate to 11:59 PM
SET @EndDate = DATEADD(MINUTE, -1, DATEADD(DAY, 1, @EndDate))

-dave

 

[synapsevampire]

Dave: If they passed through a date and a time other than midnite time that wouldn't work as you're just subtracting one minute from 1 day forward.

You'll need to pass through the date and time from CR, although you could override the time portion passed by elaborating on the SQL that Dave described.

Another option is to use a varchar() to pass through just the date portion, and then cast/convert it into a datetime within your SP.

SQL Server doesn't support just a date type, hence the nuisance.

-k

 

[Luvsql]

So, basically it is Crystal that is passing the time as well as the date. I just don't understand why I can just pass a date in query analyzer and it bringing the correct data (ie don't require time even though the parameter is setup as datetime in the sp).

 

[vidru]

Crystal will send the time because there isn't a pure Date datatype in SQL Server. So if your stored procedure parameters are DATETIME or SMALLDATETIME, then Crystal will always send the time.

The example I showed you was how I have dealt with the same situation. Most of my clients don't care about the Time portion of the database's Date/Time fields, and don't want to be bothered with remembering to enter the time as well as the date for every report.

Hope that sheds some light...

-dave

 

[vidru]

Oh, and if you're going to try my conversion method, I typed it wrong. It should be this:

  SELECT @StartDate = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), @StartDate,101))
  SELECT @EndDate = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), @EndDate,101))
  -- Set @EndDate to 11:59 PM
  SET @EndDate = DATEADD(MINUTE, -1, DATEADD(DAY, 1, @EndDate))

-dave

 

[synapsevampire]

Because QA allows for just a date, CR does not.

QA is doing an implicit conversion for you ('2003-05-09'
is not a date, it's a string), the SP still requires the time, and QA is passing one, it may even be wrong (as dave's solution was addressing - check the trace utility in SQL Server to trap what's being passed).

Try using that syntax in other tools and you'll see that they'll reject it, you explicitly ask for a datetime, so you should pass one.

-k

 

[Luvsql]

It appears that I receive the same data as QA if I enter the date in CR, then 12:00:00AM as the time (as all the time values in our database are set to 00:00:00:000). Crystal does split them into two different fields when prompted for the parameters.

Since I am knew to creating sp's, where should I add this syntax ie after I declare the two data parameters?

The thing that I don't understand that is if I just use a table of view, the option under File > report Options &quot;convert date-time to date&quot; fixes everything ie I never have any issue with dates using tables and views; Only sp's.

 

[FVTrainer]

To answer your question on where you add the syntax, and following up using Dave's example (does that violate some rule of the forum?), your statement for creating the stored procedure would start of like this:

CREATE PROC my_stored_procedure
( @StartDate datetime,
@EndDate datetime
)
AS
SELECT @StartDate = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), @StartDate,101))
SELECT @EndDate = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), @EndDate,101))
-- Set @EndDate to 11:59 PM
SET @EndDate = DATEADD(MINUTE, -1, DATEADD(DAY, 1, @EndDate))

The rest of your stored procedure would be placed after the above code.

Dave: Why not use
SET @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))

 

[Luvsql]

Yes, this works now, great! Thank you everyone for your comments!