Split date ranges into weeks

Asked

Viewed 706 times

1

Would have some simple way to extract the weeks given a range of dates, for example:

Start date: 01/07/2017

end date: 30/07/2017

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

Expected result:

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

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

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

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

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

Thank you!!!

sql sql-server query

  • see if you see:https://stackoverflow.com/questions/35025717/get-week-number-of-month-sql-server

  • Hello, There are two forms that have already been posted earlier, both are good. Follow the links below. link https://answall.com/questions/75726/pega-data-de-inicio-e-de-fim-de-umaweeklydo-m%C3%Aas ou link https://stackoverflow.com/questions/350717/get-week-number-of-month-sql-server

  • @Renanbessa: Had the opportunity to evaluate Sorack’s response? Your return is important for employees.

1 answer

0


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

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 command explanation WITH in answer to the question Using WITH AS command in Sql Server.

Observing: The values reported in the question do not correspond to what would be observed normally, since the first week would end on Saturday and not on Wednesday.

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.