Get all sunday and saturday in a year in sql server

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

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 <= @ToDate

AND AllDays >= @FromDate

select * from #TEMP_EveryWk_Sndays

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amitesh Verma11y ago
http://forums.asp.net/t/1795730.aspx?Sql+Query+For+Getting+Sundays+And+Saturdays+in+a+given+Year
Upendra Pratap Shahi11y ago
If helpful , kindly accept the answer.
Upendra Pratap Shahi11y ago
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 Shanu11y ago
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 <= @ToDate
AND AllDays >= @FromDate

select * from #TEMP_EveryWk_Sndays
Rajeesh Menoth11y ago
Hi,

declare @startDate date,@enddate date
set @startDate='01/01/2012'
set @enddate='12/31/2012'
while @startDate<=@enddate
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