nordicgirl
asked on
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.
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.
oops, i meant: SELECT DATEADD(d, @Days, '02/28/2011')
ASKER
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.
Try this:
DATEADD(d, Convert(@Days as INt), '02/28/2011')
DATEADD(d, Convert(@Days as INt), '02/28/2011')
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.
P.
ASKER
tried the convert command with no luck.
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.
The field in the table, can you tell what datatype is that.
ASKER
the datatype of the field in the table is datetime
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
did not get an answer; ended up using a stored proc to get around the issue.
declare @Days int
select @Days = 5
DATEADD(d, @Days, '02/28/2011')