sqlsql-servert-sqldatediffdatepart

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

Solution

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