SQL Dynamic DatePart when using DateDiff

Question

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

It's not much better than a case statement but you could use the dreaded dynamic sql — Michael Haren, May 07 '09 at 14:29
@Robin Day: I didn't know about this until i read BOL. Great catch! — dance2die, May 07 '09 at 14:34

score 24 · Answer 1 · answered Jul 11 '13 at 06:29

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

score 24 · Accepted Answer · answered May 07 '09 at 14:31

24

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.

answered May 07 '09 at 14:31

dance2die

35,807
39
131
194

1

Could you please explain why you prefer CASE over dynamic SQL? IMO I prefer dynamic SQL because it is less coding than having to write out a case for every possible datepart. – John Odom May 04 '15 at 22:55

score 2 · Answer 3 · edited Jan 25 '13 at 16:27

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)

score 2 · Answer 4 · answered May 07 '09 at 14:47

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.

Unfortunately, the main reason for needing this is that I want to do a proper Month datediff, rather than a 28/30/31 days. I'm actually going back to this route though I think and will just inform the user its last 30/60/90 days rather than actual months. — Robin Day, May 07 '09 at 15:11

Bas Wieringa · Answer 5 · 2013-11-13T15:27:33.397

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

score 0 · Answer 6 · answered May 07 '09 at 14:31

0

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.

answered May 07 '09 at 14:31

Andomar

232,371
49
380
404

SQL Dynamic DatePart when using DateDiff

6 Answers6

Linked

Related