March 12, 2012 at 4:53 pm
Hi !
I have a SP which accepts Date Parameter and in SSRS i have i chosen to allow Multiple Date Values and I was getting the error saying that It could not convert Nvarchar to date ..Please help me in resolving this issue ..The Issue is......... its getting the date parameter as ('MM/DD/YYYY','MM/DD/YYYY', 'MM/DD/YYYY',... ) from SSRS Please find my Storproc as below
Create proc [dbo].[Usp_HolidaySales]
@StartDate Date
as
begin
Select gblstore.Name AS StoreName,
[4-4-5 Daily Real].[Year] As [Year],
[4-4-5 Daily Real].[Date] As [Date],
[4-4-5 Daily Real].[WkDay],
DayPart.Name AS DayPart,
Category.Name AS CategoryName,
'Net Sales',
Sum(dpvHstSalesByInterval.Amount) AS NetSalesAmount
FROM ([4-4-5 Daily Real]
INNER JOIN
(Category INNER JOIN (dpvHstSalesByInterval INNER JOIN gblStore ON dpvHstSalesByInterval.FKStoreId=gblStore.StoreId)
ON Category.CategoryId=dpvHstSalesByInterval.TypeId) ON [4-4-5 Daily Real].Date=dpvHstSalesByInterval.DateOfBusiness) INNER JOIN DayPart ON dpvHstSalesByInterval.Period=DayPart.DayPartId
Where ((([4-4-5 Daily Real].[Date] IN(@StartDate))) AND ((dpvHstSalesByInterval.Type)=52)) and (Category.Name <>'Other')
GROUP BY gblstore.Name,
[4-4-5 Daily Real].[Year],
[4-4-5 Daily Real].[Date],
[4-4-5 Daily Real].[WkDay],
DayPart.Name,
Category.Name
END
March 13, 2012 at 2:45 am
i would format the parameter into an iso date standard
so when you pass it into the dataset use an expression like this
=CDate(Format(Parameters!StartDate.Value, "yyyy-MM-dd"))
One thing I learnt the hard way is the in SSRS and Dates, MM is month and mm is minutes quite an easy one to get confused on
The expressions will convert it to a date CDate and format it in YMD so that SQL can interpret the date.
March 13, 2012 at 8:46 am
So Is it applicable for passing the Multiple Dates in to the Parameter .. I was using the @StartDate in StoredProcedure Which is a DateTime DataType and from SSRS i was passing the Multiple Dates in to the @StartDate Parameter and I did As you said but its not working ...
March 13, 2012 at 8:56 am
does the stored procedure handle multiple dates being passed in with the startdate parameter?
March 13, 2012 at 8:59 am
No it doen't handle multiple Dates ... I need help in changing the Stored procedure to handle multiple Dates ... Thanks in Advance ..
March 13, 2012 at 9:09 am
are you wanting the users to be able to use the data picker, as I have just created a test report allowing multiple values for a date time parameter and it needs to have a list of avilable values set as the date picker doesnt work for multiple values
March 13, 2012 at 9:13 am
I have One Data Set which brings up Multiple Dates from a Table and User can Select Multiple dates from that drop down list ...
March 13, 2012 at 9:18 am
might want to take a look at this url http://www.codeproject.com/Articles/5986/Passing-a-Comma-Delimited-Parameter-to-a-Stored-Pr
March 13, 2012 at 11:36 am
It looks like you might have a couple of problems. The first is multi value inputs to stored procedure parameters. I like the table valued function approach to handling this issue. You can find it here:
http://www.bidn.com/blogs/MikeDavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs
I believe the other issue is that your table is expecting dates, and you are presenting it a string with your IN clause. Using the table value function like the one listed above may resolve this for you. If it does not then I would try wrapping the "Item" value from the table value function in your stored procedure with a convert such as CONVERT(DATETIME, Item)
March 13, 2012 at 12:24 pm
Thank you every one my issue was resolved as you all said after removing the comma delimiter in my input Date string parameter ..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply