SQL WHERE取决于星期几 [英] 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.

我目前有以下内容，但由于语法错误而无法使用.可以在WHERE子句中执行CASE WHEN吗?

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

推荐答案

使用一系列逻辑or和and运算符创建此逻辑要容易得多:

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

这篇关于SQL WHERE取决于星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！