Assign T-SQL variable from CASE statement referencing previously declared variables

Question

I am attempting to dynamically define a time period for a query and am running into an issue while converting the user input into a datetime format as shown below:

declare @ObjectName nvarchar(256) = '%',
        @TimePeriod int = '1',
        @TimeInterval nvarchar(128) = 'month',
        @PastPeriodDate datetime


SET @PastPeriodDate = 
  CASE @TimeInterval 
    WHEN 'hour'     THEN (select DATEADD(hour,-@TimePeriod,getdate())
    WHEN 'day'      THEN (select DATEADD(day,-@TimePeriod,getdate())
    WHEN 'week'     THEN (select DATEADD(week,-@TimePeriod,getdate())
    WHEN 'month'    THEN (select DATEADD(month,-@TimePeriod,getdate())
    WHEN 'year'     THEN (select DATEADD(year,-@TimePeriod,getdate())
    ELSE ''
  END

print @PastPeriodDate

I need to convert from '1' 'week' for example into a datetime string exactly 1 week prior to the time executed and have done this in the past, but never with a case statement involved. This is to filter results in a following query with a where clause:

select * 
from table1 t1
where t1.time > @PastPeriodDate

I'm receiving the following output when attempting to run this query:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ELSE'.

Any advice on where I'm going wrong or a more efficient way to do this would be appreciated.

Answer 1

Looks like you are missing a parenthesis on each WHEN line. You should be able to fix and simplify like this:

    DECLARE @ObjectName NVARCHAR(256) = '%' ,
    @TimePeriod INT = '1' ,
    @TimeInterval NVARCHAR(128) = 'month' ,
    @PastPeriodDate DATETIME


SET @PastPeriodDate = CASE @TimeInterval
                        WHEN 'hour'
                        THEN DATEADD(hour, @TimePeriod * -1, GETDATE())
                        WHEN 'day'
                        THEN DATEADD(day, @TimePeriod * -1, GETDATE())
                        WHEN 'week'
                        THEN DATEADD(week, @TimePeriod * -1, GETDATE())
                        WHEN 'month'
                        THEN DATEADD(month, @TimePeriod * -1, GETDATE())
                        WHEN 'year'
                        THEN DATEADD(year, @TimePeriod * -1, GETDATE())
                        ELSE ''
                      END

PRINT @PastPeriodDate