Search code examples
sql-serverparametersdateadd

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

Solution

  • 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.

    (For extra clarity, the snippet above is an error. This is the code that would generate the error from the question.)