Get all sunday and saturday in a year in sql server

Question

Amitesh Verma · Answer

http://forums.asp.net/t/1795730.aspx?Sql+Query+For+Getting+Sundays+And+Saturdays+in+a+given+Year

Upendra Pratap Shahi · Answer

If helpful , kindly accept the answer .

Upendra Pratap Shahi · Answer

DECLARE @Year AS INT , @FirstDateOfYear DATETIME , @LastDateOfYear DATETIME -- You can change @year to any year you desire SELECT @year = 2015 SELECT @FirstDateOfYear = DATEADD ( yyyy , @Year - 1900 , 0 ) SELECT @LastDateOfYear = DATEADD ( yyyy , @Year - 1900 + 1 , 0 ) -- Creating Query to Prepare Year Data ; WITH cte AS ( SELECT 1 AS DayID , @FirstDateOfYear AS FromDate , DATENAME ( dw , @FirstDateOfYear ) AS Dayname UNION ALL SELECT cte . DayID + 1 AS DayID , DATEADD ( d , 1 , cte . FromDate ), DATENAME ( dw , DATEADD ( d , 1 , cte . FromDate )) AS Dayname FROM cte WHERE DATEADD ( d , 1 , cte . FromDate ) @LastDateOfYear ) SELECT FromDate AS Date , Dayname FROM CTE WHERE DayName IN ( 'Saturday' , 'Sunday' ) OPTION ( MaxRecursion 370 ) OutPut- 2015-01-03 00:00:00.000 Saturday 2015-01-04 00:00:00.000 Sunday 2015-01-10 00:00:00.000 Saturday 2015-01-11 00:00:00.000 Sunday 2015-01-17 00:00:00.000 Saturday 2015-01-18 00:00:00.000 Sunday 2015-01-24 00:00:00.000 Saturday 2015-01-25 00:00:00.000 Sunday 2015-01-31 00:00:00.000 Saturday 2015-02-01 00:00:00.000 Sunday 2015-02-07 00:00:00.000 Saturday 2015-02-08 00:00:00.000 Sunday 2015-02-14 00:00:00.000 Saturday 2015-02-15 00:00:00.000 Sunday 2015-02-21 00:00:00.000 Saturday 2015-02-22 00:00:00.000 Sunday 2015-02-28 00:00:00.000 Saturday 2015-03-01 00:00:00.000 Sunday 2015-03-07 00:00:00.000 Saturday 2015-03-08 00:00:00.000 Sunday 2015-03-14 00:00:00.000 Saturday 2015-03-15 00:00:00.000 Sunday 2015-03-21 00:00:00.000 Saturday 2015-03-22 00:00:00.000 Sunday 2015-03-28 00:00:00.000 Saturday 2015-03-29 00:00:00.000 Sunday 2015-04-04 00:00:00.000 Saturday 2015-04-05 00:00:00.000 Sunday 2015-04-11 00:00:00.000 Saturday 2015-04-12 00:00:00.000 Sunday 2015-04-18 00:00:00.000 Saturday 2015-04-19 00:00:00.000 Sunday 2015-04-25 00:00:00.000 Saturday 2015-04-26 00:00:00.000 Sunday 2015-05-02 00:00:00.000 Saturday 2015-05-03 00:00:00.000 Sunday 2015-05-09 00:00:00.000 Saturday 2015-05-10 00:00:00.000 Sunday 2015-05-16 00:00:00.000 Saturday 2015-05-17 00:00:00.000 Sunday 2015-05-23 00:00:00.000 Saturday 2015-05-24 00:00:00.000 Sunday 2015-05-30 00:00:00.000 Saturday 2015-05-31 00:00:00.000 Sunday 2015-06-06 00:00:00.000 Saturday 2015-06-07 00:00:00.000 Sunday 2015-06-13 00:00:00.000 Saturday 2015-06-14 00:00:00.000 Sunday 2015-06-20 00:00:00.000 Saturday 2015-06-21 00:00:00.000 Sunday 2015-06-27 00:00:00.000 Saturday 2015-06-28 00:00:00.000 Sunday 2015-07-04 00:00:00.000 Saturday 2015-07-05 00:00:00.000 Sunday 2015-07-11 00:00:00.000 Saturday 2015-07-12 00:00:00.000 Sunday 2015-07-18 00:00:00.000 Saturday 2015-07-19 00:00:00.000 Sunday 2015-07-25 00:00:00.000 Saturday 2015-07-26 00:00:00.000 Sunday 2015-08-01 00:00:00.000 Saturday 2015-08-02 00:00:00.000 Sunday 2015-08-08 00:00:00.000 Saturday 2015-08-09 00:00:00.000 Sunday 2015-08-15 00:00:00.000 Saturday 2015-08-16 00:00:00.000 Sunday 2015-08-22 00:00:00.000 Saturday 2015-08-23 00:00:00.000 Sunday 2015-08-29 00:00:00.000 Saturday 2015-08-30 00:00:00.000 Sunday 2015-09-05 00:00:00.000 Saturday 2015-09-06 00:00:00.000 Sunday 2015-09-12 00:00:00.000 Saturday 2015-09-13 00:00:00.000 Sunday 2015-09-19 00:00:00.000 Saturday 2015-09-20 00:00:00.000 Sunday 2015-09-26 00:00:00.000 Saturday 2015-09-27 00:00:00.000 Sunday 2015-10-03 00:00:00.000 Saturday 2015-10-04 00:00:00.000 Sunday 2015-10-10 00:00:00.000 Saturday 2015-10-11 00:00:00.000 Sunday 2015-10-17 00:00:00.000 Saturday 2015-10-18 00:00:00.000 Sunday 2015-10-24 00:00:00.000 Saturday 2015-10-25 00:00:00.000 Sunday 2015-10-31 00:00:00.000 Saturday 2015-11-01 00:00:00.000 Sunday 2015-11-07 00:00:00.000 Saturday 2015-11-08 00:00:00.000 Sunday 2015-11-14 00:00:00.000 Saturday 2015-11-15 00:00:00.000 Sunday 2015-11-21 00:00:00.000 Saturday 2015-11-22 00:00:00.000 Sunday 2015-11-28 00:00:00.000 Saturday 2015-11-29 00:00:00.000 Sunday 2015-12-05 00:00:00.000 Saturday 2015-12-06 00:00:00.000 Sunday 2015-12-12 00:00:00.000 Saturday 2015-12-13 00:00:00.000 Sunday 2015-12-19 00:00:00.000 Saturday 2015-12-20 00:00:00.000 Sunday 2015-12-26 00:00:00.000 Saturday 2015-12-27 00:00:00.000 Sunday

Syed Shanu · Answer

Check this query Declare @FromDate VARCHAR(20) = '2015-08-01'--DATEADD(mm,-12,getdate()) Declare @ToDate VARCHAR(20) = '2016-08-06'--DATEADD(mm, 1, getdate()) -- used for the pivot table result DECLARE @MyColumns AS NVARCHAR(MAX), @SQLquery AS NVARCHAR(MAX) -- 2.This Temp table is to created for get all the days between the start date and end date to display as the Column Header --2.Start ///////////// IF OBJECT_ID('tempdb..#TEMP_EveryWk_Sndays') IS NOT NULL DROP TABLE #TEMP_EveryWk_Sndays DECLARE @TOTALCount INT Select @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate); WITH d AS ( SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id), REPLACE(@FromDate,'-','')) FROM sys.all_objects ) SELECT distinct DATEADD(DAY, 0 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE)) as Saturday , DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE)) as Sunday into #TEMP_EveryWk_Sndays FROM d where AllDays AND AllDays >= @FromDate select * from #TEMP_EveryWk_Sndays

Rajeesh Menoth · Answer

Hi, declare @startDate date,@enddate date set @startDate='01/01/2012' set @enddate='12/31/2012' while @startDate begin if(DATENAME(dw,@startDate)='Sunday' or DATENAME(dw,@startDate)='Saturday') insert into MyHoliday(Holiday_date,Specification) values(convert(date,@startDate,103),DATENAME(dw,@startDate)) set @startDate=DATEADD(DD,1,@startDate) end Ref this : http://forums.asp.net/t/1795730.aspx?Sql+Query+For+Getting+Sundays+And+Saturdays+in+a+given+Year