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

本文介绍了计算错误的两天之间的周数和部分周数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

为什么返回 4 而不是 6?

Why is this returning 4 instead of 6?

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

作为日期部分的周是否仅计算 monday - sunday(整周)的周数?如何获得所有星期 - 包括那些没有 7 天 的星期?在上述情况下，答案应为 6.

Is week as datepart calculating only weeks from monday - sunday (whole weeks)? How to get all weeks - including those which doesn't have seven days ? In the case above answer should be 6.

推荐答案

DATEDIFF 计算转换，而不是句点(例如查看DATEDIFF(year,'20161231','20170101')).它还将星期日视为一周的第一天.那么，我们如何补偿这些特征呢?首先，我们改变日期，使星期一成为新的星期日，其次我们加 1 以补偿 Fence-Post 错误:

DATEDIFF counts transitions, not periods (e.g. look at DATEDIFF(year,'20161231','20170101')). It also treats Sunday as the first day of the week. So, how do we compensate for these features? First, we shift our dates so that Mondays are the new Sundays, and second we add 1 to compensate for the Fence-Post error:

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 表以显示您期望的结果.

If this doesn't match what you want, perhaps you can adopt and adapt my @Samples table to show the results you do expect.

这篇关于计算错误的两天之间的周数和部分周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！