# 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
Monday, January 6, 2020 6:10 PM

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

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

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Monday, January 6, 2020 6:45 PM
• 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
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

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Tuesday, January 7, 2020 1:59 AM

### All replies

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

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

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Monday, January 6, 2020 6:45 PM
• 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.

Monday, January 6, 2020 7:00 PM
• 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 