CASE statement in WHERE clause using OR operator

Question

I use a SELECT query to get data from a calendar TABLE. I want the data when we are today except on Fridays when I want the data for Friday, Saturday and Sunday. Here is my query:

SELECT *
 FROM Calendar
 WHERE entry_date =
 CASE 
 WHEN DATEPART(dw,GETDATE()) = 6
 THEN  CAST(GETDATE() AS DATE) OR DATEADD(day, 1, CAST(GETDATE() AS DATE)) OR DATEADD(day, 2, CAST(GETDATE() AS DATE))
 ELSE
 CAST( GETDATE()AS DATE)
 END;

which gives me incorrect syntax

@McNets here is the error message: `Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'OR'.` — Rich, Jan 07 '17 at 14:16
Please edit your question with additional details, don't use the comments. — Mark Rotteveel, Jan 07 '17 at 15:29
If your `CASE` worked what would `entry_date = CAST(GETDATE() AS DATE) OR DATEADD(day, 1, CAST(GETDATE() AS DATE)) OR DATEADD(day, 2, CAST(GETDATE() AS DATE))` mean? [This](http://stackoverflow.com/a/10260297/925460) answer may provide a little clarity re: using `CASE` in a conditional expression. — HABO, Jan 07 '17 at 16:33

score 1 · Answer 1 · answered Jan 07 '17 at 14:17

1

Try this:

WHERE entry_date = CAST(GETDATE() AS DATE) OR
      (DATEPART(dw, GETDATE()) = 6 AND 
       entry_date IN (CAST(GETDATE() + 1 AS DATE), CAST(GETDATE() + 2 AS DATE))
      )

Note that this uses the shorthand notation of + 2 to add two days to a datetime value. If you prefer, dateadd() can be used instead.

answered Jan 07 '17 at 14:17

Gordon Linoff

1,242,037
58
646
786

This works but that gives me the Friday's results even if today is not a Friday. The day that I run the query should gives me this resuls only on Friday, the other weekday I would like to have the data only for the day I run it. – Rich Jan 07 '17 at 14:21
@Rich . . . This the exact logic of the query you were attempting to write. I don't see how it would give Friday's results on other days. – Gordon Linoff Jan 07 '17 at 14:26

score 0 · Accepted Answer · answered Jan 07 '17 at 14:17

Try this:

SELECT *
FROM Calendar
WHERE (DATEPART(dw,GETDATE()) = 6
AND entry_date              IN (CAST(GETDATE() AS DATE), DATEADD(DAY, 1, CAST(GETDATE() AS DATE)), DATEADD(DAY, 2, CAST(GETDATE() AS DATE))))
OR (DATEPART(dw,GETDATE())   <> 6
AND entry_date               = CAST( GETDATE()AS DATE));

CASE statement in WHERE clause using OR operator

2 Answers2