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

How do I exclude Weekend days in a SQL Server query?(如何在 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)

这篇关于如何在 SQL Server 查询中排除周末?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！