Can you pass a variable into the interval of DATEDIFF

Question

I have a feeling I already know the answer but can you do the following (most basic example)

DECLARE @interval varchar(2) = 'yy'

SELECT DATEDIFF(@interval, myDate, GETDATE())

I'm holding the unit (yy, mm or dd) in a column and want to dynamically run DATEDIFF based on one of the other columns, passing through the interval.

Answer 1

You can't. You need to use a case expression:

select (case @interval
            when 'year' then datediff(year, mydate, getdate())
            when 'month' then datediff(month, mydate, getdate())
            when 'day' then datediff(day, mydate, getdate())
        end)

I should say. You could use dynamic SQL, but that is generally unnecessarily complicated for this type of calculation.