Number of weeks and partial weeks between two days calculated wrong

Question

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))

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´ doesn't use ´datefirst´ setting :( Look here: http://stackoverflow.com/questions/1101892/is-it-possible-to-set-start-of-week-for-t-sql-datediff-function — Arvo, Mar 13 '17 at 07:28
@GiorgosAltanis - january 2017 has 6 weeks. Sunday is the only day in the first week of january. — FrenkyB, Mar 13 '17 at 07:33
Sorry, I had the wrong impression that you want number of weeks having a working day. I have deleted the answer. — Giorgos Altanis, Mar 13 '17 at 07:35
@FrenkyB January doesn't have 6 weeks. No month has 42 days. It doesn't have six week transitions either, it has 4. It seems that you want to perform a completely different type of calculation - how many weeks get covered by a specific interval? — Panagiotis Kanavos, Mar 13 '17 at 08:29
@FrenkyB in other words, please fix the title and question to ask what you really mean. The only calculation that would return 6 has nothing to do with *number of weeks **between** two days* — Panagiotis Kanavos, Mar 13 '17 at 08:30
@PanagiotisKanavos - I believe the OPs logic is that there is a *partial week* at the start, then 4 complete weeks, then another partial week at the end, for the period 20170101 - 20170131, per the OPs definition of week (Monday - Sunday). Both of the partial weeks are being "rounded up" in the count. — Damien_The_Unbeliever, Mar 13 '17 at 08:36
@Damien_The_Unbeliever that's not "weeks between", it's "weeks covered by a interval". No method that calculates differences will return this value. Both start and end dates would have to be adjusted to their respective week's start and end, in order to use `DATEDIFF` — Panagiotis Kanavos, Mar 13 '17 at 08:39
@Damien_The_Unbeliever this could be answered trivially with a Calendar table, it would be the count of `DISTINCT WeekNumer` values for dates between the start and end value. — Panagiotis Kanavos, Mar 13 '17 at 09:05

score 5 · Accepted Answer · answered Mar 13 '17 at 08:21

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

Results:

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

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

can I ask you how you've got result in 'Results' - with line under head columns and aligned to columns? Have you copied this somehow from SSMS? — FrenkyB, Mar 17 '17 at 12:12
@FrenkyB - "Results to Text" instead of "Results to Grid" - Available via the "Query -> Results To" menu or one of the toolbar buttons (in my current setup, ~10 icons to the right of the "Execute" button) (Although I also did a sneaky cheat and block edited it to remove a fair chunk of wasted space since I did my usual "pick absurd data types if none are provided" when defining the `@Samples` table) — Damien_The_Unbeliever, Mar 17 '17 at 12:16

score 2 · Answer 2 · answered Mar 13 '17 at 09:44

What you ask though, is how many weeks are covered by a range, not how many weeks are between two dates.

DATEDIFF always uses Sunday when calculating week transitions. This isn't a bug, it's done to ensure the function is deterministic and returns the same value, for every query, no matter the DATEFIRST setting. From the documentation

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

One solution would be to calculate the difference between the week numbers of the start and end dates, when the first day is Monday. 1 is added to the difference to take account of the first week as well:

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

That's a fragile calculation though that breaks if DATEFIRST changes or if one of the dates is on a different year.

You could use ISO Weeks to get rid of SET DATEFIRST:

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

but that would fail for 2017-01-01 because Sunday is counted as Week 52 of the previous year.

A far better solution though would be to count the distinct week numbers using a Calendar table that contains dates and different week numbers to cover multiple business requirements, eg both normal and ISO Week numbers, or business calendars based on a 4-4-5 calendar.

In this case, you could just count distinct week numbers:

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

If the table doesn't have an ISO Week column, you can use DATEPART:

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

Number of weeks and partial weeks between two days calculated wrong

2 Answers2

Linked