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

Question

Entering to get all Sunday dates and Saturday dates between given date ranges I used a iterative solution , as shown 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 do this using the set based approach . Is there a way to get the result above using the set based approach ?

The result obtained using an iterative solution is given below.

Answer 1

Check it out with CTE:

 Declare @DateFrom DateTime ='2011-07-01', @DateTo DateTime = '2011-07-31' ;WITH CTE(dt) AS ( Select @DateFrom Union All Select DATEADD(d,1,dt)FROM CTE Where dt<@DateTo ) Select DATENAME(dw,dt) day, dt from CTE where DATENAME(dw,dt)In('Sunday' , 'Saturday') --To understand more, comment above select and run this. select * from ( select 'Sunday' day,dt from CTE where DATENAME(dw,dt)In('Sunday' ) union select 'Saturday',dt from CTE where DATENAME(dw,dt)In('Saturday' ) ) a order by dt 

Check out this link to understand both approaches.

Answer 2

There really is no set-based approach when you start with an empty set. You can replace your code with recursive CTE. You can get 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); 

Please note that this does not check the requirements of the day of the week. This is just a week from day one.

Answer 3

This should solve it using a table of tables:

 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 NN)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 Enhanced 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 NN)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