Number of Sat, Sun in a year

Question

User-1471881183 posted

Hello all,

May i know is there any short way

1. to find how many Saturdays, Sundays in a year?
2. what is the last date of Saturday in a year?
3. what is the last date of Sunday in a year?if i pass the year then query has to return the above output

Answer 1

User77042963 posted

declare @yr int=2019

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
  
,mydates as (
select 
dateadd(day,n-1,datefromparts(@yr,1,1)) dt from Nums 
where dateadd(day,n-1,datefromparts(@yr,1,1)) <datefromparts(@yr+1,1,1) 
)

select   
Sum(Case When datepart(weekday,dt)=7 then 1 else null end) TotalSaturdays 
,Sum(Case When datepart(weekday,dt)=1 then 1 else null end) TotalSundays
,Max(Case When datepart(weekday,dt)=7 then dt else null end) maxSaturday
,Max(Case When datepart(weekday,dt)=1 then dt else null end) maxSunday

from mydates

Answer 2

User77042963 posted

If you have a calendar table in your database, you have use a simple select query with aggregate functions to get your result.

Answer 3

User-719153870 posted

Hi winseealn,

In addition to @limno's reply, you can also refer to below demo which is built based on very basic Transact-SQL and simple logic:

declare @year int=2019
declare @startdate date=convert(date,convert(varchar(50),@year)+'-01-01')
declare @enddate date=convert(date,convert(varchar(50),@year)+'-12-31')
declare @maxsaturday date
declare @maxsunday date
declare @totalSaturday int=0
declare @totalSunday int=0
while @startdate<=@enddate
begin
if(DATENAME(DW,@startdate)='Saturday')
begin
set @totalSaturday+=1
set @maxsaturday=@startdate
end
else if(DATENAME(DW,@startdate)='Sunday')
begin
set @totalSunday+=1
set @maxsunday=@startdate
end
set @startdate=DATEADD(DD,1,@startdate)
end
select @totalSaturday as totalSaturday,@totalSunday as totalSunday,@maxsaturday as maxsaturday,@maxsunday as maxsunday

set any value for the parameter @year and run the query, below is the result when @year = 2019:

Best Regard,

Yang Shen