CODEWITHSUNDEEP
sql-servert-sql
Farhad-Taran
Farhad-Taran

Reputation: 6540

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

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

Upvotes: 8

Views: 16752

Answers (2)

1nstinct
1nstinct

Reputation: 1775

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

Upvotes: 0

Taryn
Taryn

Reputation: 247870

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.

Upvotes: 14

Related Questions