Using Parameters in DATEADD function of a Query

问题:

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

回答1:

It sounds like you're passing the decimal as the 3rd instead of the 2nd parameter to DATEADD(), like:

DATEADD(day, GETDATE(), @days)

Although the snippet in the question looks fine.

回答2:

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.

回答3:

The following code works perfectly fine here (SQL Server 2005, executed in Management Studio):

DECLARE @days decimal
SET @days = -10

SELECT DATEADD(day, @days, GETDATE())

as does the following

DECLARE @days decimal
SET @days = -10

SELECT * FROM myTable WHERE myDate > DATEADD(day, @days, GETDATE())

So, the problem must lie somewhere else...

回答4:

Are you sure the error is associated with this statement? There are no decimals involved and if I try this it still works

DECLARE @days decimal (19,6)
SET @days = -10.3346

--result is actually irrelevant
IF CAST(40000.6 AS decimal (19,6)) > DATEADD(day, @days, GETDATE())
    SELECT 'yes'
ELSE
    SELECT 'no'

Even trying to cast -10 decimal to smalldatetime this gives a different error

SELECT CAST(CAST(-10 AS decimal (19,6)) AS smalldatetime)

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.