ajlearningcom (4/18/2014)
declare @DateFrom DateTime = CONVERT(DATETIME, '01/01/2014', 103)declare @DateTo DateTime = CONVERT(DATETIME, '01/03/2014', 103)
;WITH CTE(dt)
AS
(
Select @DateFrom
Union All
Select DATEADD(d,1,dt)FROM CTE
Where dt<@DateTo
)
select DayName,CONVERT(varchar(50), dt, 103)as Date from (select 'Saturday' as DayName,dt from CTE
where DATENAME(dw,dt)In('Saturday') group by dt
Union
select 'Sunday'as DayName,dt from CTE
where DATENAME(dw,dt)In('Sunday'))as result order by dt
Reference : http://aj-learning.com/blog/get-weekend-date-sql-server-specific-date-range/
Please read the following article for why you should generally avoid rCTEs that "count" by 1 like yours does. There are several much more efficient ways to do the same thing.
[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)