title: T-SQL CASE in the WHERE Clause using BETWEEN dates
body: <p>Having a problem with the syntax, need to return a result based on Number and date range, if its 6,2 then this date range else the other date range.</p>
  
  <pre><code>DECLARE @Test TABLE 
  
  (
  ID NVARCHAR(10)
  ,Number INT
  ,[Date] DATETIME
  
  )
  INSERT INTO @Test VALUES ('TG32',6,'20160715')
  INSERT INTO @Test VALUES ('TG47',6,'20160803')
  INSERT INTO @Test VALUES ('AG9',6,'20160805')
  INSERT INTO @Test VALUES ('BF27',2,'20160804')
  INSERT INTO @Test VALUES ('QD65',2,'20160802')
  INSERT INTO @Test VALUES ('F98',0,'20160806')
  INSERT INTO @Test VALUES ('GC5',0,'20160731')
  INSERT INTO @Test VALUES ('HT76',0,'20160802')
  INSERT INTO @Test VALUES ('KL81',0,'20160805')
  INSERT INTO @Test VALUES ('WR52',0,'20160802');
  
  SELECT TOP 10 * FROM @Test
  
  WHERE [Date] = CASE WHEN Number IN ('6','2') THEN  ([Date] BETWEEN CAST(GETDATE() - 6 AS date) AND GETDATE()) 
  ELSE ([Date] BETWEEN CAST(GETDATE() - 1 AS date) AND GETDATE()) END 
  </code></pre>