Hi 

Good Morning!!

I want go get all weekends of the year   (year dynamic) with out CTE concept,because I need to implement in 2005 version.I have googled but getting only CTE examples.

could you please help me query to get the weekends in a year.

Thank you for your help.

Thanks & Regards

Hi Chantan,

Check this example for year 2015

Declare @beginDate Date, @EndDate Date 
Select @beginDate = '01/01/2015', @EndDate = '12/31/2015'
Declare @Calendar Table 
(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)
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 
    ,DATEPART(Year, @beginDate) As YearNo 
    ,DATEPART(QUARTER, @beginDate) As QuarterNo 
    ,DATEPART(MONTH, @beginDate) As MonthNo 
    ,DATEPART(DayOfYear, @beginDate) As DayOfYearNo 
    ,DATEPART(Day, @beginDate) As DayNo     
    ,DATEPART(Week, @beginDate) As WeekNo
    ,DATEPART(WEEKDAY, @beginDate) As WeekDayNo 
  Set @beginDate = DateAdd(Day, 1, @beginDate)
End
Select * From @Calendar Where IsWeekend = 1 

Hi Chantan,

CTE is introduced since SQL Server 2005, see Using Common Table Expression. Please see code below that has been tested in SQL Server 2005.

DECLARE @startDate datetime; 
SET @startDate = '20150101';  
   
SET DATEFIRST 1;       -- First day of week 
 
;WITH  
Sundays AS 
    (SELECT DATEADD(DAY,7-DATEPART(weekday,@startDate),@startDate) DT
	 UNION ALL
	 SELECT DATEADD(WEEK,1,DT) FROM Sundays
	 WHERE DT<DATEADD(YEAR,+1,@startDate)
    ) 
,WeekEnds AS
(
SELECT DT,'Sunday' we FROM Sundays	
UNION ALL
SELECT DATEADD(DAY,-1,DT),'Saturday' we FROM Sundays
)
SELECT DT,we FROM WeekEnds
WHERE DT BETWEEN @startDate AND DATEADD(YEAR,+1,@startDate)
ORDER BY DT

 

Best practice in this case would be using a calendar table, you can query the calendar table rather than generate the weekends again and again.

If you have any question, feel free to let me know.

CTE is supported in SQL Server 2005