Search code examples
sql-server-2014ssms-2014

T-SQL CASE in the WHERE Clause using BETWEEN dates


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.

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 

Solution

  • You can't use CASE in this way, in sql case is not like if then else it is more similar to function (test?if_yes;if_not)

    WHERE [Date] BETWEEN CAST(GETDATE() - CASE WHEN Number IN ('6','2') THEN 6 ELSE 0 END AS date) AND GETDATE()