Generate weeks range between 2 date ranges

Question

I am using SQL Server 2017 and I need to get the week ranges between 2 date ranges. I found a similar question week ranges But it will not work all scenarios

Code:

declare @sDate date='2020-04-24',
@eDate date='2020-05-07';

;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

Expected output: (week first as Sunday)

@smor i Have added code what solution given.. i already mentioned input...pls check before comment — Ajt, Apr 30 '20 at 02:27
have a look at this - having a calendar table is worth it's weight in gold - https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server — Harry, Apr 30 '20 at 03:04
@Harry thnks..but i actually nedd weekwise info not day wise — Ajt, Apr 30 '20 at 04:07

score 1 · Accepted Answer · answered Apr 30 '20 at 05:03

Since you wanted the week to start from Sunday, then the end date of your anchor member should be set to Saturday.

And the start date for the recursive member just add 1 day from the end date of the cte

declare @sDate date = '2020-04-24',
        @eDate date = '2020-05-07';

with 
cte as
(
    select  @sDate as StartDate, 
            convert(date , dateadd(wk, datediff(wk, 0, @sDate), 5)) as EndDate

    union all

    select  dateadd(day, 1, EndDate) as StartDate,
            case when dateadd(week, 1, EndDate) <= @eDate 
                 then dateadd(week, 1, EndDate) 
                 else @eDate 
                 end as EndDate
    from    cte
    where   dateadd(day, 1, EndDate) <= @eDate
)
select  *
from    cte

Generate weeks range between 2 date ranges

1 Answers1