本文介绍了计算所有“星期日,星期一...星期六”在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.

然而,它依赖于设置。

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两天之间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-24 01:13