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-12getdate()) 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_dateSpecification) values(convert(date@startDate103)DATENAME(dw@startDate)) set @startDate=DATEADD(DD1@startDate) end Ref this : http://forums.asp.net/t/1795730.aspx?Sql+Query+For+Getting+Sundays+And+Saturdays+in+a+given+Year