John Cobby John Cobby - 13 days ago 5
SQL Question

SQL WHERE depending on day of week

I have a requirement to check records up to differing dates, depending on which day of the week it is currently.

On a Friday I need for it to look at the entire next week, until Sunday after next. On any other day it should check the current week, up until the coming Sunday.

I have the below currently but it's not working due to syntax error. Is it possible to do a

CASE WHEN
inside a
WHERE
clause?

WHERE
T0.[Status] IN ('R','P')
AND
CASE
WHEN DATEPART(weekday,GETDATE()) = '5'
THEN T0.[DueDate] >= GETDATE() AND <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())
WHEN DATEPART(weekday, GETDATE()) != '5'
THEN T0.[DueDate] >= GETDATE() AND <= DATEADD(DAY ,8- DATEPART(weekday, GETDATE()), GETDATE())
END

Answer

It's much easier to create this logic with a series of logical or and and operators:

WHERE
T0.[Status] IN ('R','P') AND
((DATEPART(weekday,GETDATE()) = '5' AND 
  T0.[DueDate] >= GETDATE() AND 
  T0.[DueDate] <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())) OR
 (DATEPART(weekday,GETDATE()) != '5' AND 
  T0.[DueDate] >= GETDATE() AND 
  T0.[DueDate] <= DATEADD(DAY ,8- DATEPART(weekday,GETDATE()),GETDATE())
)