Get specific dates between given date-ranges using set based approach

Question

In-order to get all the Sunday dates and Saturday dates between given date ranges I have used a iterative solution as below,

create  TABLE #Dayweeks (id int IDENTITY(1,1),StartWeek DATETIME, EndWeek DATETIME)

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'



WHILE (@wkstartdate <= @wkenddate)
    BEGIN
         INSERT INTO #Dayweeks
         (
         StartWeek, EndWeek
         )
         SELECT
         @wkstartdate, DATEADD(wk,DATEDIFF(wk,0,@wkstartdate),6)-1

         SELECT @wkstartdate = DATEADD(dd,7,@wkstartdate)
    END

I wonder how to achieve this using set based approach. Is there any way to get the above result by using set based approach?

The output I got using iterative solution is given below.

Answer 1

There really is no "set-based" approach when you are starting with an empty set. You can replace your code with a recursive CTE. You can get the start dates by doing:

with weeks as (
      select @wkstartdate as dte
      union all
      select dateadd(weeks, 1, dte)
      from dte
      where dte < @wkenddate
)
insert into #Dayweeks(Startweek, EndWeek)
    select dte, dateadd(day, 6, dte)
    from weeks
option (maxrecursion 0);

Note that this does not verify the day of the week requirements. It just counts weeks from the first day.

Answer 2

This should solve it using a tally table:

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
, alldays as
(
  SELECT
    top (datediff(d, @wkstartdate, @wkenddate)) 
      cast(dateadd(d, N-1, @wkstartdate) as date) day
  FROM tally
)
SELECT day
FROM alldays
WHERE datediff(d, 0, day) % 7 in(5,6)

EDIT Improved version:

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT
  CAST(DATEADD(d, 0, N) as DATE) WEEKEND
FROM tally
WHERE 
  N between datediff(d, 0, @wkstartdate) and datediff(d, 0, @wkenddate)
  AND N % 7 in(5,6)

Result:

day
2015-12-06
2015-12-12
2015-12-13
...
...
2016-04-03