计算所有“星期日，星期一...星期六”在SQL Server两天之间 [英] Calculate all "Sundays,Mondays...Saturdays" between two days in SQL Server

问题描述

我想在两天之间计算总人数星期日，星期一...星期六。我想要一个选择查询，因为这是强制性的，因为我正在工作的情况。

I want to calculate total say Sundays,Mondays...Saturdays between two days. I want a do it in a select query because that is mandatory according to the situation where I'm currently working.

我有一些工作代码，我可以计算所有星期日，但在星期一不工作

I have some working code where I'm able to calculate all sundays but not working in case of mondays

DECLARE  @StartDate DATE = '2011-10-01',
         @EndDate   DATE = '2011-10-31' 

SELECT DayCount = count(* )
FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                        [Date] = dateadd(DAY,ROW_NUMBER()
                  OVER(ORDER BY c1.name),
                  DATEADD(DD,-1,@StartDate))
        FROM   [master].[dbo].[spt_values] c1 ) x
WHERE  datepart(dw,[Date]) = 1;

推荐答案

我认为您的查询提供正确的结果，但可能简化了一点。

I think your query delivers the correct result but could be simplified a bit.

然而，它依赖于 SET DATEFIRST 设置。

datepart（dw，[Date]）= 1 如果 SET DATEFIRST 为1，则会计算周一的数量。

datepart(dw,[Date]) = 1 will count the number of Mondays if SET DATEFIRST is 1.

尝试这样：

set datefirst 7 -- Sunday
select datepart(dw, '20111227')
set datefirst 1 -- Monday
select datepart(dw, '20111227')

结果：

-----------
3

-----------
2

更新：
另一个查询执行相同的操作。

Update: Another query that does the same.

select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
      dateadd(day, Number.number, @StartDate) <= @EndDate and
      datepart(dw, dateadd(day, Number.number, @StartDate)) = 1

这篇关于计算所有“星期日，星期一...星期六”在SQL Server两天之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！