我正在尝试选择SQL Server中比当前日期晚3天的记录,但我需要排除周末(星期六和星期日)。
以下SQL脚本用于选择超过3天的记录:
select * from [dbo].[tb_Schedule]
WHERE date >= DATEADD(day, -3, GETDATE())但问题是,我不知道如何排除周末。
我知道下面的Where子句用于排除周末,但我不知道如何在上面的where子句中实现它
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)请帮助我如何选择大于3天但不包括周末的记录。谢谢
发布于 2019-02-13 01:27:23
这里有一个关于如何排除周末的公式和示例。正如我们之前提到的,考虑使用日程表,因为您可能想要排除假日。
SELECT calDate,
DATENAME( dw, calDate),
DATEADD( DD, -( 3 + CASE WHEN DATEDIFF( DD, '19000106', calDate)%7 <= 2 THEN DATEDIFF( DD, '19000106', calDate)%7
WHEN DATEDIFF( DD, '19000106', calDate)%7 = 3 THEN 2
ELSE 0 END), calDate)
FROM (VALUES( CAST( '20190201' AS date)),
( CAST( '20190202' AS date)),
( CAST( '20190203' AS date)),
( CAST( '20190204' AS date)),
( CAST( '20190205' AS date)),
( CAST( '20190206' AS date)),
( CAST( '20190207' AS date)),
( CAST( '20190208' AS date)),
( CAST( '20190209' AS date)),
( CAST( '20190210' AS date)),
( CAST( '20190211' AS date)),
( CAST( '20190212' AS date)),
( CAST( '20190213' AS date)),
( CAST( '20190214' AS date)),
( CAST( '20190215' AS date)))x(calDate);发布于 2019-02-13 04:51:12
如果您只是排除周末而不是节假日,那么您可以使用AND将子句连接起来。这篇文章使用带有DATEPART的WEEKDAY,因为它更容易阅读。不过,这在节假日是行不通的。
SELECT *
FROM [dbo].[tb_Schedule]
WHERE
[date] >= DATEADD(DAY, -3, GETDATE())
AND DATEPART(WEEKDAY,[date]) NOT IN (1,7)发布于 2019-02-13 06:09:47
select * from [dbo].[tb_Schedule]
where date >= DATEADD(DD, -3 -case SIGN((DATEPART(DW, GETDATE()) + @@DATEFIRST + 5) % 7 - 3)
when -1 then 2 else 0 end, GETDATE())仅当您在星期天调用此select时才会失败(仅返回2天)。
此外,如果您只需要GetDate()的date部分,则可以将最后一个GETDATE()替换为FLOOR(CAST(GETDATE() as real))
https://stackoverflow.com/questions/54655156
复制相似问题