ODBC-在SQL中排除周末

Question

我目前有一个SELECT语句，它允许我获得昨天的日期：

SELECT DATEADD(DD, -1, CAST(GETDATE() as DATE))

但是，当日期是星期一时，我需要返回星期五而不是星期天的值日期的结果。当星期二到星期五时，它将是上面提到的SELECT语句。

当星期一是星期五，当星期二到星期五是-1天时，要添加哪个代码行才能获得值？

谢谢你的帮助。

Answer 1

以下是除了星期一(现在将是-3(之外的所有日子的-1天

select DATEADD(DD, (case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 else -1 end ), CAST(GETDATE() as DATE))

如果您需要周六和周日也显示周五：

select DATEADD(DD, (
case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 
when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Sunday' then -2 
when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Saturday' then -1 
else -1 end 
), CAST(GETDATE() as DATE))

或者，如果您需要周六和周日显示当前日期：

select DATEADD(DD, (
case when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Monday' then -3 
when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Sunday' then 0 
when datename( weekday, DATEADD(DD, -1, CAST(GETDATE() as DATE))  ) like 'Saturday' then 0 
else -1 end 
), CAST(GETDATE() as DATE))