使用DateDiff时的SQL Dynamic DatePart [英] SQL Dynamic DatePart when using DateDiff

问题描述

有没有办法将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 （参数部分）for 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.

这篇关于使用DateDiff时的SQL Dynamic DatePart的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！