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

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

Answer 2

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.

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