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:-