how to get the start and end dates of all weeks between two dates in SQL server?

Question

I need to get all week start and end dates(weeks) between two dates and then run a query returning the number of records inserted in each of those weeks.

declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-03-25';

--query to get all weeks between sDate and eDate

--query to return number of items inserted in each of the weeks returned



WEEK                  NoOfItems
-----------------------------------------
2013-02-25            5
2013-03-4             2
2013-03-11            7

Taryn · Accepted Answer · 2013-02-25T16:30:55.833

14

You can use a recursive CTE to generate the list of dates:

;with cte as
(
  select @sDate StartDate, 
    DATEADD(wk, DATEDIFF(wk, 0, @sDate), 6) EndDate
  union all
  select dateadd(ww, 1, StartDate),
    dateadd(ww, 1, EndDate)
  from cte
  where dateadd(ww, 1, StartDate)<=  @eDate
)
select *
from cte

See SQL Fiddle with Demo.

Then you can join this to your table, to return the additional details.

edited Feb 25 '13 at 16:30

answered Feb 25 '13 at 16:19

Taryn

242,637
56
362
405

is it possible to get the end of week date of that week in the EndDate column? – Farhad-Taran Feb 25 '13 at 16:23
1

Note that this will fail in certain circumstances when the dates are a certain "distance" apart. Whereby you'll hit a maximum recursion level. In order to avoid this, append "OPTION (MAXRECURSION 0)" after your "select * from cte" – pim Aug 29 '16 at 19:47
Is it possible to set monday as the first day? – FrenkyB Mar 14 '17 at 19:55
@FrenkyB You'd want to look at setting [DATEFIRST](https://msdn.microsoft.com/en-us/library/ms181598.aspx). – Taryn Mar 14 '17 at 20:14
@FrenkyB I think it might help you https://stackoverflow.com/a/66593958/2467036 – 1nstinct Mar 12 '21 at 03:54

score 0 · Answer 2 · answered Mar 12 '21 at 03:52

Here is my solution. Inspired by this answer

DECLARE @sDate DATE = DATEADD(MONTH, -6, GETDATE())
DECLARE @eDate DATE = GETDATE()
;WITH cte AS
(
  SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @sDate), 0) AS StartDate, DATEADD(WEEK, DATEDIFF(WEEK, 0, @sDate), 6) AS EndDate
  UNION ALL
  SELECT DATEADD(WEEK, 1, StartDate), DATEADD(WEEK, 1, EndDate)
  FROM cte
  WHERE DATEADD(WEEK, 1, StartDate) <= @eDate
)

SELECT * FROM cte

how to get the start and end dates of all weeks between two dates in SQL server?

2 Answers2

Linked

Related