SQL WHERE depending on day of week

Question

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

Yes, it is possible - but it's generally a bad idea. Use AND/OR instead. — jarlh, Nov 25 '16 at 10:57
Are you absolutely sure you phrased your requirements correctly? Note that they mean that on Friday, you need to look nine days ahead, but on Saturday, only one. If instead, on Saturday, you need to look eight days ahead, you can simply your logic: look up to the next Friday, plus two additional days. — , Nov 25 '16 at 11:19
Yeah, hvd. It seems wrong but that's how it was described to me. I guess the idea is that it won't be checked on the weekend anyway so that's okay. However I see your point and that would probably be much simpler now that I think about it. — coblenski, Nov 25 '16 at 11:30

score 4 · Accepted Answer · answered Nov 25 '16 at 10:59

4

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())
)

answered Nov 25 '16 at 10:59

Mureinik

297,002
52
306
350

1

Amazing, seems so obvious now that I see it written out. Many thanks. – coblenski Nov 25 '16 at 11:06

score 1 · Answer 2 · edited Nov 25 '16 at 11:16

Your syntax is wrong, you are using a condition evaluation in the THEN clause instead of an assignemnet

  WHEN DATEPART(weekday,GETDATE()) = '5' THEN  Your_column1  ELSE your_column2 END
  ......

or a inner case

  CASE
    WHEN DATEPART(weekday,GETDATE()) = '5' THEN 
            CASE  WHEN T0.[DueDate] >= GETDATE() 
                    AND <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())  
                    THEN Your_column1  ELSE your_column2 
            END 
 END
   ......

SQL WHERE depending on day of week

2 Answers2

Linked