T-SQL to Display Weekends Between two Dates

Different ways to find the weekend between two given dates. The SQL requires @begindate and @endate paramteters to be entered in the below SQL

Download T-SQL WeekendBetweenTwoValidDates

DECLARE @beginDate Date='20150101',
		@endDate Date='20150131'


DECLARE @Calendar Table
(CalendarDate Date Primary key, IsWeekend Bit)

WHILE @beginDate <= @endDate
BEGIN
INSERT INTO @Calendar
SELECT
@beginDate As CalendarDate
,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
Set @beginDate = DateAdd(Day, 1, @beginDate)
End

SELECT CalendarDate From @Calendar Where IsWeekend = 1

On SQL 2012 or higher version you can use choose function.

http://msdn.microsoft.com/en-us/library/hh213019.aspx

DECLARE @beginDate Date='20150101',
 @EndDate Date='20150131'


Declare @Calendar Table
(CalendarDate Date Primary key, IsWeekend varchar(20))

While @beginDate <= @endDate
Begin
Insert Into @Calendar
SELECT 
@beginDate As CalendarDate,
CHOOSE(DATEPART(dw, @beginDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') 
Set @beginDate = DateAdd(Day, 1, @beginDate) 
End

Select CalendarDate From @Calendar where IsWeekend='WEEKEND'

Using Number table

Declare @beginDate date = '20150101', @endDate date = '20150131'

SELECT 
DATEADD(dd, Number, @beginDate)
from
(
SELECT
 (a.Number * 256) + b.Number AS Number
FROM 
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) a (Number),
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) b (Number)
)T
Where
Number >= 0
and DATEADD(dd, Number, @beginDate) <= @endDate
and DATEPART(dw, DATEADD(dd, Number, @beginDate)) in ( 1, 7 )

Ouput:-