使用 DateDiff 时的 SQL 动态 DatePart

2022-01-01 00:00:00 datetime sql sql-server-2005 tsql sql-server

有没有办法将 DateDiff 的 DatePart 参数作为变量传递?以便我可以编写与此类似的代码?

Is there a way to pass the DatePart parameter of DateDiff as a variable? So that I can write code that is similar to this?

DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT

SELECT @datePart = 'dd'
SELECT @dateParameter = 28

SELECT
    *
FROM
    MyTable
WHERE
    DATEDIFF(@datePart, MyTable.MyDate, GETDATE()) < @dateParameter

我能想到的唯一方法是使用 CASE 语句检查参数的值，或者将 SQL 构建为字符串并在 EXEC 中运行它.

The only ways I can think of doing it are with a CASE statement checking the value of the parameter or by building the SQL as a string and running it in an EXEC.

有人有任何更好"的建议吗?平台为MS SQL Server 2005

Does anyone have any "better" suggestions? The platform is MS SQL Server 2005

推荐答案

根据 DATEDIFF(参数部分)用于 SQL Server 2005，

According to BOL entry on DATEDIFF (arguments section) for SQL Server 2005,

这些日期部分和缩写不能作为用户声明的变量提供.

These dateparts and abbreviations cannot be supplied as a user-declared variable.

因此，您可能一直在使用动态 SQL 或使用 CASE 语句.但我会选择 CASE 版本而不是动态 SQL.

So you are probably stuck with Dynamic SQL or using a CASE statement. But I would opt for a CASE version instead of dynamic SQL.

相关文章