We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Number of Sat, Sun in a year[Answered] RSS

## 3 replies

Last post Jan 07, 2020 01:59 AM by Yang Shen

• winseealn@ho...

Member

297 Points

1356 Posts

### Number of Sat, Sun in a year

Jan 06, 2020 06:10 PM|winseealn@hotmail.com|LINK

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
• limno

All-Star

123252 Points

10024 Posts

Moderator

### Re: Number of Sat, Sun in a year

Jan 06, 2020 06:45 PM|limno|LINK

```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
)

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```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• limno

All-Star

123252 Points

10024 Posts

Moderator

### Re: Number of Sat, Sun in a year

Jan 06, 2020 07:00 PM|limno|LINK

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

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Number of Sat, Sun in a year

Jan 07, 2020 01:59 AM|Yang Shen|LINK

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