Divide date range in weeks

Navigation
1

It would have some simple way of extracting the weeks given a range of dates, for example:

start date: 07/01/2017

end date: 7/30/2017

Given the dates above how could you automate the extraction of the weeks contained in this range?

Expected result:

Week 1 = 07/01/2017 to 05/07/2017

Week 2 = 06/07/2017 to 07/12/2017

Week 3 = 7/13/2017 to 7/19/2017

Week 4 = 07/20/2017 to 07/26/2017

Week 5 = 07/27/2017 to 07/30/2017

Thank you !!!

    
asked by anonymous 20.07.2017 / 18:12

1 answer

0

You can use the WITH clause to recursively retrieve dates for the desired weeks: 

WITH semanas
  AS (SELECT 1 AS ordem,
             CAST('2017-07-01' AS DATE) AS inicio,
             DATEADD(DAY, -1, DATEADD(WEEK, 1, CAST('2017-07-01' AS DATE))) AS fim
      UNION ALL
      SELECT s.ordem + 1,
             DATEADD(WEEK, 1, s.inicio),
             DATEADD(DAY, -1, DATEADD(WEEK, 2, s.inicio))
        FROM semanas s
       WHERE s.fim < '2017-07-30')
SELECT s.ordem,
       CONVERT(VARCHAR, s.inicio, 103) AS inicio,
       CONVERT(VARCHAR, s.fim, 103) AS fim
  FROM semanas s
 ORDER BY s.ordem
OPTION(MAXRECURSION 0);

The values shown will be: 

╔═══╦═══════╦════════════╦════════════╗
║   ║ ordem ║ inicio     ║ fim        ║
╠═══╬═══════╬════════════╬════════════╣
║ 1 ║ 1     ║ 01/07/2017 ║ 07/07/2017 ║
║ 2 ║ 2     ║ 08/07/2017 ║ 14/07/2017 ║
║ 3 ║ 3     ║ 15/07/2017 ║ 21/07/2017 ║
║ 4 ║ 4     ║ 22/07/2017 ║ 28/07/2017 ║
║ 5 ║ 5     ║ 29/07/2017 ║ 04/08/2017 ║
╚═══╩═══════╩════════════╩════════════╝

We have a great explanation of the WITH command in the question answer Using WITH AS command on Sql Server .

Note: The values entered in the question do not match what would normally be observed, since the first week ends on Saturday rather than on Wednesday.

    
20.07.2017 / 19:32
Doubt with Expression Language JSTL JSP Delete a row in DB