• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

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.

0
nordicgirl
Asked:
nordicgirl
  • 5
  • 4
  • 2
1 Solution
 
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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:
Never mind, I decided to use a stored proc instead. thanks anyway.
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now