代码之家 › 专栏 › 技术社区 › Robin Day

使用datediff时的SQL动态日期部分

datetime tsql sql-server-2005 sql-server sql

Robin Day · 技术社区 · 16 年前

有没有方法将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

6 回复 | 直到 12 年前

dance2die 16 年前

根据上的BOL条目 DATEDIFF ( 争论第)节)对于SQL Server 2005,

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

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

Asken 12 年前

很遗憾,旧的但仍然有效。

我是这样做的,只是想分享代码,这样你就不必做我必须做的所有恼人的输入。涵盖所有可能的日期部分。只需替换函数名和要为其他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

ahains 16 年前

除了建议的动态SQL或case语句之外,您唯一真正能做的就是始终在粒度日期部分执行datediff,然后进行upconvert。不过,这并不是一个简单的例子,如果您试图用datediff在太大的范围内细化一个部分,例如datediff(second,0,getdate()),那么函数中就会溢出。但是,如果你只需要一些小部件,你就可以了(仔细检查一下你关心的最大日期值)。

例如

select datediff(minute, 0, getdate())

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

Jon Egerton Aditya kumar sahoo 12 年前

当然,使用下面这样的动态查询非常有效,我假设您计划只使用@datepart的缩写。我建议在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)

Andomar 16 年前

我觉得没有比你描述的更好的方法了。SQL Server可能会将datediff查询编译为一组依赖于datepart参数的操作。所以你需要一个案例或者动态查询。

Bas Wieringa 12 年前

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

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