5

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

Damien
  • 13,927
  • 14
  • 55
  • 88
  • works fine for sql server 2008 management studio. Which sql server are you using. – Saar Nov 23 '09 at 14:30
  • 1
    I suggest giving us closer to the actual code rather than this basic version which nobody can find any problem with. – MartW Nov 23 '09 at 15:40

4 Answers4

13

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.

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
4

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

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    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. http://technet.microsoft.com/en-us/library/ms186819.aspx – Spikeh Feb 11 '14 at 14:04
  • 3
    @spikeh the above code highlights the snippet that contains the error. Why would you expect it to work? – Andomar Feb 11 '14 at 14:11
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...

Heinzi
  • 167,459
  • 57
  • 363
  • 519
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.
gbn
  • 422,506
  • 82
  • 585
  • 676