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

works fine for sql server 2008 management studio. Which sql server are you using. — Saar
– Saar, Commented Nov 23, 2009 at 14:30
I suggest giving us closer to the actual code rather than this basic version which nobody can find any problem with. — MartW
– MartW, Commented Nov 23, 2009 at 15:40

Nathan Griffiths · Accepted Answer · 2015-06-15 01:26:02Z

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.

Andomar · Accepted Answer · 2019-04-26 13:18:39Z

5

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

edited Apr 26, 2019 at 13:18

answered Nov 23, 2009 at 14:15

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

Spikeh Over a year ago

The documentation for DATEADD() (and my experience using it) suggests this is inaccurate. This is a bug in SSIS - the above code will not work in any version of SQL. technet.microsoft.com/en-us/library/ms186819.aspx

Andomar Over a year ago

@spikeh the above code highlights the snippet that contains the error. Why would you expect it to work?

Heinzi · Accepted Answer · 2009-11-23 14:11:47Z

0

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

answered Nov 23, 2009 at 14:11

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Comments

gbn · Accepted Answer · 2009-11-23 15:16:09Z

0

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.

answered Nov 23, 2009 at 15:16

gbn

435k85 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

Using Parameters in DATEADD function of a Query

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related