SQL Dynamic DatePart when using DateDiff

问题:

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

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.

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

回答1:

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

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

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.

回答2:

Old but still valid unfortunately.

I did it the case way and just want to share the code so you don't have to do all the annoying typing I had to do. Covers all possible date parts. Just replace the name of the function and the date function to implement for other T-SQL date functions.

Copy and paste section

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

回答3:

The only thing you can really do aside from the suggested dynamic sql or case statement is to always do the datediff at a granular DatePart and then upconvert. This isn't fool proof though, you will get an overflow in the function if you try to datediff to granular a part over too large a span e.g. datediff(second, 0, getdate()). But if you just need something like minute parts you should be fine (double check with max date values you care about).

So for example

select datediff(minute, 0, getdate())

If I want to convert this to hours, days, etc, I can just divide the result by the appropriate amount. It won't take into account leap years etc.

回答4:

Definitely using the dynamic query such as this below works very well, I assume you plan on using only abbreviation for @datePart. I would recommend using a minimum of VARCHAR(4) for datepart this will handle such abbreviations as yyyy and mcs. Happy coding:

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)

回答5:

I don't think there are better ways then you describe. Sql Server probably compiles the DATEDIFF query to a set of operations that depend on the datepart parameter. So you'd need a CASE, or dynamic queries.

回答6:

I created a Scalar values function to do this. It is based on the solutions mentioned above but easier to implement in your code

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

In a query you can use this function like this:

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