如何在 SQL Server 查询中排除周末?

2021-12-10 00:00:00 date weekend tsql sql-server

如何排除 DateTime 列中星期六或星期日的值?

How do I exclude values in a DateTime column that are Saturdays or Sundays?

例如，给定以下数据:

date_created
'2009-11-26 09:00:00'  -- Thursday
'2009-11-27 09:00:00'  -- Friday
'2009-11-28 09:00:00'  -- Saturday
'2009-11-29 09:00:00'  -- Sunday
'2009-11-30 09:00:00'  -- Monday

这是我正在寻找的结果:

this is the result I'm looking for:

date_created
'2009-11-26 09:00:00'  -- Thursday
'2009-11-27 09:00:00'  -- Friday
'2009-11-30 09:00:00'  -- Monday

谢谢！

推荐答案

在处理星期几计算时，重要的是要考虑当前的 DATEFIRST 设置.此查询将始终正确排除周末，使用 @@DATEFIRST 考虑一周第一天的任何可能设置.

When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

相关文章