Weekend days
declare
@DateFrom DateTime=CONVERT(DATETIME,'01/01/2014', 103)
declare @DateTo DateTime=CONVERT(DATETIME,'01/03/2014', 103)
;WITH CTE(dt)
AS
(
Select
@DateFrom
UnionAll
SelectDATEADD(d,1,dt)FROM CTE
Where
dt<@DateTo
)
selectDayName,CONVERT(varchar(50), dt, 103)asDatefrom (select'Saturday'asDayName,dt from CTE
whereDATENAME(dw,dt)In('Saturday')groupby dt
Union
select'Sunday'asDayName,dt from CTE
whereDATENAME(dw,dt)In('Sunday'))as result orderby dt