Report Services Wont execute My SP

desireemm · December 13th, 2007, 11:23 PM

Everytime I try to execute my SP in Report Services this is the message I get
I can execute it in SQL Server and have no problems at all it gives me exactly what I ask for. Help please

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

------------------------------
ADDITIONAL INFORMATION:

Failed to convert parameter value from a String to a DateTime. (System.Data)

------------------------------

String was not recognized as a valid DateTime. (mscorlib)

Code:

ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartDateserved datetime = null,
@EndDateserved datetime = null,
@Enter_LastName nvarchar(25) = null, 
@Enter_Duration nvarchar(10)  = null
AS 
SELECT      IR#, [Date served], [Reason for Exclusion], Duration, [First Name], [Last Name]
FROM          [dbo].[Extended Exclusions]
WHERE  (@StartDateserved is null or [Date served] >= @StartDateserved) 
AND (@EndDateserved is null or [Date served] <= @EndDateserved)
AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) 
AND (@Enter_Duration is null or Duration = @Enter_Duration)

anothervbaddict · December 21st, 2007, 03:46 AM

Is the date parameter from your report template being passed to the stored procedure as a string?

Can alter your stored procedure to something similar to this:

ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartDateserved varchar(12),
@EndDateserved varchar(12),
...
...
WHERE (@StartDateserved is null or [Date served] >= convert(datetime,@StartDateserved,103))
...
...