使用 DateDiff 时的动态 DatePart

Question

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

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 中运行它。

有人有任何“更好”的建议吗？ 平台是MS SQL Server 2005

Answer 1

不幸的是旧的但仍然有效。

我按照案例的方式做了，只是想分享代码，这样你就不必做我必须做的所有烦人的打字。 涵盖所有可能的日期部分。 只需替换函数名称和日期函数即可为其他 T-SQL 日期函数实现。

复制并粘贴部分

-- SELECT dbo.fn_DateAddFromStringPart('year', 1, GETDATE())
CREATE FUNCTION fn_DateAddFromStringPart
(
    @Interval VARCHAR(11),
    @Increment INT,
    @Date SMALLDATETIME
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @NewDate DATETIME

    -- Add the T-SQL statements to compute the return value here
    SELECT @NewDate = CASE
        WHEN @Interval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, @Increment, @Date)
        WHEN @Interval IN ('quarter', 'qq', 'q') THEN DATEADD(QUARTER, @Increment, @Date)
        WHEN @Interval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, @Increment, @Date)
        WHEN @Interval IN ('dayofyear', 'dy', '') THEN DATEADD(DAYOFYEAR, @Increment, @Date)
        WHEN @Interval IN ('day', 'dd', 'd') THEN DATEADD(DAY, @Increment, @Date)
        WHEN @Interval IN ('week', 'wk', 'ww') THEN DATEADD(WEEK, @Increment, @Date)
        WHEN @Interval IN ('weekday', 'dw', 'w') THEN DATEADD(WEEKDAY, @Increment, @Date)
        WHEN @Interval IN ('hour', 'hh') THEN DATEADD(HOUR, @Increment, @Date)
        WHEN @Interval IN ('minute', 'mi', 'n') THEN DATEADD(MINUTE, @Increment, @Date)
        WHEN @Interval IN ('second', 'ss', 's') THEN DATEADD(SECOND, @Increment, @Date)
        WHEN @Interval IN ('millisecond', 'ms') THEN DATEADD(MILLISECOND, @Increment, @Date)
        WHEN @Interval IN ('microsecond', 'mcs') THEN DATEADD(MICROSECOND, @Increment, @Date)
        WHEN @Interval IN ('nanosecond', 'ns') THEN DATEADD(NANOSECOND, @Increment, @Date)
    END

    -- Return the result of the function
    RETURN @NewDate

END
GO

Answer 2

根据 DATEDIFF 上的 BOL 条目（对于 SQL Server 2005，参数部分），

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

因此，您可能会陷入动态 SQL 或使用 CASE 语句的困境。
但我会选择 CASE 版本而不是动态 SQL。

Answer 3

除了建议的动态 sql 或 case 语句之外，您真正可以做的唯一一件事就是始终在粒度 DatePart 处执行 datediff，然后进行上转换。 但这并不是万无一失的，如果您尝试使用 datediff 在太大的跨度上细化某个部分，例如 datediff(second, 0, getdate())，您将在函数中遇到溢出。 但如果你只需要像微小部分这样的东西，你应该没问题（仔细检查你关心的最大日期值）。

例如，

select datediff(minute, 0, getdate())

如果我想将其转换为小时、天等，我可以将结果除以适当的数量。 它不会考虑闰年等。

Answer 4

当然，使用如下所示的动态查询效果很好，我假设您计划仅使用 @datePart 的缩写。 我建议对日期部分至少使用 VARCHAR(4)，这将处理 yyyy 和 mcs 等缩写。 快乐编码：

DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT
DECLARE @SQLTemp varchar(MAX)


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

set  @SQLTemp ='SELECT * FROM MyTable
    WHERE DATEDIFF('+@datePart+', '+MyTable.MyDate+', GETDATE()) < '+@dateParameter

exec (@SQLTemp)

Answer 5

我认为没有比你描述的更好的方法了。 Sql Server 可能会将 DATEDIFF 查询编译为一组依赖于 datepart 参数的操作。 所以你需要一个 CASE，或者动态查询。

Answer 6

我创建了一个标量值函数来执行此操作。 它基于上面提到的解决方案，但更容易在代码中实现

CREATE FUNCTION [dbo].[dynamic_dateadd] 
(
@unit varchar(5),
@number int,
@dt datetime
)
RETURNS datetime
AS
BEGIN
    declare @result datetime
    if (@unit='M') BEGIN SET @result=(select DATEADD(M,@number,@dt)) END
    if (@unit='WW') BEGIN SET @result=(select DATEADD(WW,@number,@dt)) END
    if (@unit='D') BEGIN SET @result=(select DATEADD(D,@number,@dt)) END
    if (@unit='H') BEGIN SET @result=(select DATEADD(HH,@number,@dt)) END
    return(@result)
END

在查询中，您可以像这样使用此函数：

select startdate, validity_unit, validity_number,
dbo.dynamic_dateadd(valididy_unit,validity_number,startdate) as enddate
from SALES_subscription