Using Parameters in DATEADD function of a Query.

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.