title: Assign T-SQL variable from CASE statement referencing previously declared variables
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select * 
  from table1 t1
  where t1.time &gt; @PastPeriodDate
  </code></pre>
  
  <p>I'm receiving the following output when attempting to run this query:</p>
  
  <pre><code>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'.
  </code></pre>
  
  <p>Any advice on where I'm going wrong or a more efficient way to do this would be appreciated.</p>