两天之间的几周数量和部分周的计算错误

Question

为什么这个返回4而不是6？

SET DATEFIRST 1
SELECT DATEDIFF(WEEK, CAST('2017-01-01' AS DATE), CAST('2017-01-31' AS DATE))

一周作为日期派对仅计算monday - sunday（整周）的数周？如何全周 - 包括没有seven days的那些？在上面的情况下，答案应为 6。

Answer 1

DATEDIFF计数 transitions ，而不是周期（例如，查看 DATEDIFF(year,'20161231','20170101')）。它也将周日视为一周的第一天。那么，我们如何补偿这些功能？首先，我们改变日期，以便星期一是新的星期日，其次，我们添加1个以补偿围栏错误：

declare @Samples table (
    StartAt date not null,
    EndAt date not null,
    SampleName varchar(93) not null
)
insert into @Samples (StartAt,EndAt,SampleName) values
('20170101','20170131','Question - 6'),
('20170102','20170129','Exactly 4'),
('20170102','20170125','3 and a bit, round to 4'),
('20170101','20170129','4 and 1 day, round to 5')
--DATEDIFF counts *transitions*, and always considers Sunday the first day of the week
--We subtract a day from each date so that we're effectively treating Monday as the first day of the week
--We also add one because DATEDIFF counts transitions but we want periods (FencePost/FencePanel)
select *,
    DATEDIFF(WEEK, DATEADD(day,-1,StartAt), DATEADD(day,-1,EndAt)) +1
    as NumWeeks
from @Samples

结果：

StartAt    EndAt      SampleName                 NumWeeks
---------- ---------- -------------------------- -----------
2017-01-01 2017-01-31 Question - 6               6
2017-01-02 2017-01-29 Exactly 4                  4
2017-01-02 2017-01-25 3 and a bit, round to 4    4
2017-01-01 2017-01-29 4 and 1 day, round to 5    5

如果这与您想要的不匹配，也许您可以采用并调整我的@Samples表以显示您期望的结果。

Answer 2

您问的是一个范围覆盖了多少周，而不是两个日期之间有多少周。

DATEDIFF在计算周过渡时总是使用周日。这不是一个错误，它是为了确保函数是确定性的，并且对于每个查询，无论DATEFIRST设置如何，都会返回相同的值。从文档

指定设置datefirst对约会夫没有影响。约会夫总是将周日用作一周的第一天，以确保功能是确定性的。

一种解决方案是计算第一天是星期一的开始日期和结束日期的差异。1也添加到差异中，以考虑第一周：

SET DATEFIRST 1;
select 1 +datepart(WEEK,'20170131') - datepart(WEEK,'20170101') 

这是一个脆弱的计算，尽管DATEFIRST发生了变化，或者日期之一是不同的年份。

您可以使用ISO周来摆脱SET DATEFIRST：

select 1 +datepart(ISO_WEEK,'20170131') - datepart(ISO_WEEK,'20170101') 

，但对于2017-01-01来说，这将失败，因为周日被计为上一年的第52周。

一个更好的解决方案是使用包含日期和不同周数的日历表来计算不同的周数，以涵盖多个业务需求，例如正常和ISO周数字和基于4-4的业务日历-5日历。

在这种情况下，您只能计算不同的周数：

SELECT COUNT(DISTINCT Calendar.IsoWeek )
from Calendar
where date between '20170101' and '20170131'

如果表没有ISO周专栏，则可以使用DATEPART：

select count (distinct datepart(ISO_WEEK,date) )
from Calendar
where date between '20170101' and '20170131'