在查询的 DATEADD 函数中使用参数

发布于 2024-08-12 07:31:54 字数 701 浏览 2 评论 0原文

我正在尝试在查询中使用 SQL 的 DateAdd 函数。问题是当我使用参数设置第二个参数 number 参数时，我收到一个错误，该错误将显示如下内容：

无法转换参数值小数转换为日期时间

虽然如果我无参数输入它，即硬编码一个 Int，它工作正常。

这有效：

SELECT     FieldOne, DateField
FROM         Table
WHERE     (DateField> DATEADD(day, -10, GETDATE()))

而这无效：

SELECT     FieldOne, DateField
FROM         Table
WHERE     (DateField> DATEADD(day, @days, GETDATE()))

Where @days = -10

对我做错了什么有什么想法吗？顺便说一句，我正在 SQL Server Manager 中设置此变量，因为我正在尝试解决 DataAccess 代码中的错误。不确定这是否有影响。

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情何以堪。 2024-08-19 07:31:54

我知道这是一篇旧帖子，但对于遇到此问题的其他人来说，我在 Reporting Services 2008 R2 中也遇到了类似的问题，尽管错误消息是“参数数据类型 nvarchar 对于 dateadd 函数的参数 2 无效”。我认为这个问题可能是相关的。

该问题是由 Reporting Services 解析 SQL 代码以生成报表数据集的方式引起的。就我而言，我能够将此数据集查询：更改

SELECT  DateAdd(wk, @NumWeeks, calendar_date) AS ToWeekFromDate
FROM dim_date

为：

SELECT  DateAdd(wk, Convert(Int, @NumWeeks), calendar_date) AS ToWeekFromDate
FROM dim_date

并且错误已解决。

编辑：只是稍微扩展一下这个答案：问题是 Reporting Services 无法解析 @NumWeeks 的正确数据类型，我认为可能是因为它位于 DateAdd( ) 函数，并默认为 NArchar。添加显式 Convert() 将数据类型设置为 Int（即使它已经是数字）使解析器能够正确识别 @NumWeeks 的数据类型。

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.

回复收藏 0 原文

吻风 2024-08-19 07:31:54

听起来您将小数作为第三个参数而不是第二个参数传递给 DATEADD()，例如：

DATEADD(day, GETDATE(), @days)

尽管问题中的代码片段看起来不错。

（为了更加清楚起见，上面的代码片段是一个错误。这是将从问题中生成错误的代码。）

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

回复收藏 0 原文

枕头说它不想醒 2024-08-19 07:31:54

下面的代码在这里工作得很好（SQL Server 2005，在 Management Studio 中执行）：

DECLARE @days decimal
SET @days = -10

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

如下所示

DECLARE @days decimal
SET @days = -10

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

所以，问题一定出在其他地方......

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

回复收藏 0 原文

栀子花开つ 2024-08-19 07:31:54

您确定该错误与此语句相关吗？不涉及小数，如果我尝试这样做，它仍然有效

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'

即使尝试将 -10 小数转换为smalldatetime，这也会产生不同的错误

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.

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.

回复收藏 0 原文

~没有更多了~