获取两个日期之间的工作日数（星期日，星期一，星期二）SQL [英] Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

问题描述

所以，我想在开始日期和结束日期确定这两个日期之间的星期几。

So, I'd like to, for a Start Date and End Date, determine how many particular days of the week occur between these two dates.

那么如何许多星期一，星期二等

So how many mondays, tuesdays, etc

我知道我可以通过开始和结束日期之间的循环来做，并检查每一天，但差异可能是一个很大的数字的日子。我喜欢不需要循环的东西。有任何想法吗？ （必须在SQL Server 2005 +中支持）

I know I can do this with a loop between the Start and End Date and check each day, but the difference could possibly be a great number of days. I'd prefer something that didn't require a loop. Any ideas? (Must be supported in SQL Server 2005+)

推荐答案

鉴于我认为要做到这一点：

Given what I think you're trying to get, this should do it:

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

这篇关于获取两个日期之间的工作日数（星期日，星期一，星期二）SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！