cannot use a parameter for the number of days value in DateAdd for an SSRS dataset

I am having trouble with the DateAdd function in SSRS 2005.  I want the user to enter a parameter for the number of days.  I want to filter a date range in the dataset with the result set.

For example,

= DATEADD(d, 0, '02/28/2011')  works fine.

= DATEADD(d, @Days, '02/28/2011') gives me this error:

Error Source:  System.Data

Error Message:  Failed to convert parameter value from a Decimal to a DateTime.


I have tried setting the parameter data type to string, int, bool, datetime, and double and nothing works.  

Please Help!

Thanks.

nordicgirlAsked:
Who is Participating?
 
nordicgirlAuthor Commented:
Never mind, I decided to use a stored proc instead. thanks anyway.
0
 
knightEknightCommented:
It seems to work for me.  Are you sure the error is referring to this statement?


declare @Days int
select  @Days = 5
DATEADD(d, @Days, '02/28/2011')
0
 
knightEknightCommented:
oops, i meant:  SELECT DATEADD(d, @Days, '02/28/2011')
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
nordicgirlAuthor Commented:
this works ok in SQL Management Server, but not in Reporting Services.  I define the parameter in the report, link it to the dataset, and then try to filter the dataset with it.
0
 
pssandhuCommented:
Try this:

DATEADD(d, Convert(@Days as INt), '02/28/2011')
0
 
pssandhuCommented:
Also make sure the parameters data type in your report is set as "Integer" and if you are passing the parameter value to a stored proc, the parameter in the stored proc is INT data type.

P.
0
 
nordicgirlAuthor Commented:
tried the convert command with no luck.
0
 
pssandhuCommented:
The error is saying that it trying to convert from a Decimal. Can you tell what parameter or value is declared as Decimal.

The field in the table, can you tell what datatype is that.
0
 
nordicgirlAuthor Commented:
the datatype of the field in the table is datetime
0
 
pssandhuCommented:
Are you able to send me your report. Not the whole project just the report. YOu can find it in the projects folder. It will have an extension of rdl.

Change the extension to txt and upload. I won't be able to run it but I should be able to see all the settings.

P.
0
 
nordicgirlAuthor Commented:
did not get an answer; ended up using a stored proc to get around the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.