Calculate Number of INCLUDING Weeks between 2 dates

Question

I need to calculate Number of INCLUDING Weeks (even if only 1 day falls in the next week (weeks starts on Monday, ends on Sunday), count it as the week as well) between 2 dates.

If I use regular datediff, the output for both is 2.

SELECT datediff(ww, '2022-03-01','2022-03-13')
SELECT datediff(ww, '2022-03-14','2022-03-31')

But I need to get 2 and 3 number of weeks instead:

From Date	To Date	Expected
2022-03-01	2022-03-13	falls in 2 weeks (02/28/2022 - 03/06/2022 and 03/07/2022 - 03/13/2022)
2022-03-14	2022-03-31	falls in 3 weeks (03/14/2022 - 03/20/2022, 03/21/2022 - 03/27/2022 and 03/28/2022 - 04/03/2022)

This question covers fractional weeks in-depth. https://stackoverflow.com/questions/42758099/number-of-weeks-and-partial-weeks-between-two-days-calculated-wrong — Edward Radcliffe, Apr 04 '22 at 18:00
`datediff(ww, ` You save a **trivial** amount of effort using the abbreviation for "week" but the latter is far more readable and understandable. Develop good habits. — SMor, Apr 04 '22 at 18:06
Add 6 days first `SELECT datediff(week, '2022-03-01', DATEADD(day, 6, '2022-03-13'))` — Charlieface, Apr 05 '22 at 00:02
See Edward Radcliffe's link above. The [accepted answer](https://stackoverflow.com/a/42759105/8895292) should produce the desired result `DATEDIFF(WEEK, DATEADD(day,-1,StartAt), DATEADD(day,-1,EndAt)) +1` — SOS, Apr 05 '22 at 01:19
Does this answer your question? [Number of weeks and partial weeks between two days calculated wrong](https://stackoverflow.com/questions/42758099/number-of-weeks-and-partial-weeks-between-two-days-calculated-wrong) — SOS, Apr 05 '22 at 20:39

score 1 · Accepted Answer · answered Apr 05 '22 at 15:54

Here is a solution. NB SQL server count Sunday as the first day of the week. If we wish to count Monday as the first day of the week we have to remove one day from our dates, as in the second query.

CREATE TABLE dates (
From_Date date,   
To_Date   date);
INSERT INTO dates VALUES
('2022-03-01','2022-03-13'),  
('2022-03-14','2022-03-31');

SELECT
  From_Date ,
  DATENAME(ww, From_Date) FromWeek,
  To_Date,
  DATENAME(ww, To_Date) ToWeek,
  1 + CAST( DATENAME(ww, To_Date) AS INT)
    - CAST( DATENAME(ww, From_Date) AS INT) WeekDiff
FROM dates;

From_Date  | FromWeek | To_Date    | ToWeek | WeekDiff
:--------- | :------- | :--------- | :----- | -------:
2022-03-01 | 10       | 2022-03-13 | 12     |        3
2022-03-14 | 12       | 2022-03-31 | 14     |        3

SELECT
  From_Date ,
  DATENAME(ww, From_Date) FromWeek,
  To_Date,
  DATENAME(ww, To_Date) ToWeek,
  1 + CAST( DATENAME(ww, DATEADD(day, -1, To_Date)) AS INT)
    - CAST( DATENAME(ww, DATEADD(day, -1, From_Date)) AS INT) WeekDiff
FROM dates;

From_Date  | FromWeek | To_Date    | ToWeek | WeekDiff
:--------- | :------- | :--------- | :----- | -------:
2022-03-01 | 10       | 2022-03-13 | 12     |        2
2022-03-14 | 12       | 2022-03-31 | 14     |        3

db<>fiddle here

Calculate Number of INCLUDING Weeks between 2 dates

1 Answers1