I am trying to us the DateAdd function of SQL in my Query. The problem is when I use a parameter to set the second arguement, the number argument I get an error which will say something like this:
Failed to convert parameter value from a Decimal to a DateTime
While if I enter it parameterless, i.e hardcode an Int, it works fine.
This works:
SELECT FieldOne, DateField
FROM Table
WHERE (DateField> DATEADD(day, -10, GETDATE()))
while this does not:
SELECT FieldOne, DateField
FROM Table
WHERE (DateField> DATEADD(day, @days, GETDATE()))
Where @days = -10
Any ideas into what I am doing wrong? Incidentally I am setting this variable in SQL Server Manager, as I am trying to work out a bug in my DataAccess code. Not sure if that makes a difference.
Thanks
I know this is an old post, but for anyone else having this problem I had a similar issue in Reporting Services 2008 R2, although the error message was "Argument data type nvarchar is invalid for argument 2 of dateadd function." I think this issue could be related.
The problem was caused by the way Reporting Services parses the SQL code to generate a report dataset. In my case, I was able to change this dataset query:
SELECT DateAdd(wk, @NumWeeks, calendar_date) AS ToWeekFromDate
FROM dim_date
to this:
SELECT DateAdd(wk, Convert(Int, @NumWeeks), calendar_date) AS ToWeekFromDate
FROM dim_date
and the error was resolved.
EDIT: Just to expand on this answer a little: the issue was that Reporting Services was unable to parse the correct data type for @NumWeeks
, I think possibly due to it being inside the DateAdd()
function, and was defaulting it to NVarchar. Adding an explicit Convert()
to set the data type to Int (even though it was already a number) enabled the parser to correctly identify the data type for @NumWeeks
.