获取两个日期之间的工作日数（星期日，星期一，星期二）SQL

Question

因此，对于一个开始日期和结束日期，我想确定在这两个日期之间发生的一周中的特定天数。

那么多少个星期一，星期二等

我知道我可以在“开始日期”和“结束日期”之间循环并每天检查一次，但是可能相差很多天。我更喜欢不需要循环的东西。有任何想法吗？（必须在SQL Server2005+中受支持）

Answer 1

鉴于我 认为 您正在尝试获得的结果，应该这样做：

SET DATEFIRST 1

DECLARE
    @start_date DATETIME,
    @end_date DATETIME

SET @start_date = '2011-07-11'
SET @end_date = '2011-07-22'

;WITH Days_Of_The_Week AS (
    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL
    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL
    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL
    SELECT 7 AS day_number, 'Sunday' AS day_name
)
SELECT
    day_name,
    1 + DATEDIFF(wk, @start_date, @end_date) -
        CASE WHEN DATEPART(weekday, @start_date) > day_number THEN 1 ELSE 0 END -
        CASE WHEN DATEPART(weekday, @end_date)   < day_number THEN 1 ELSE 0 END
FROM
    Days_Of_The_Week