CODEWITHSUNDEEP
sql-server
Lajith
Lajith

Reputation: 1867

Generate weeks range between 2 date ranges

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)

enter image description here

Upvotes: 0

Views: 520

Answers (1)

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 1

Related Questions