How Many Sunday In Give Month & Year Script in T-SQL

Here this script calculate total Sunday in give month and give year @month = 6 and @year = 2008.


declare @year int
declare @month int
declare @totaldays int
declare @isLeapYear bit
declare @firstDayofmonth varchar(20)
declare @date varchar(20)
set @isLeapYear = 'false'
--------------------------------------------------
--- Here change year and month for diff result
--------------------------------------------------
set @year = 2008
set @month = 6
--------------------------------------------------
set @totaldays = 31
set @date = cast(@year as varchar) + '-' + cast(@month as varchar) + '-01'
if(@year % 4 = 0)
set @isLeapYear = 'true'
if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'
if(@month = 1) set @totaldays = 31
if(@month = 2 )
begin
if(@isLeapYear = 'true') set @totaldays = 29
else set @totaldays = 28
end
if(@month = 3) set @totaldays = 31
if(@month = 4) set @totaldays = 30
if(@month = 5) set @totaldays = 31
if(@month = 6) set @totaldays = 30
if(@month = 7) set @totaldays = 31
if(@month = 8) set @totaldays = 31
if(@month = 9) set @totaldays = 30
if(@month = 10) set @totaldays = 31
if(@month = 11) set @totaldays = 30
if(@month = 12) set @totaldays = 31
set @firstDayofmonth = DATENAME(dw,@date)
print 'Given Year : ' + cast(@year as varchar)
print 'Given Month : ' + cast(@month as varchar)
print 'Total days : ' + cast(@totaldays as varchar)
print 'First day is : ' + @firstDayofmonth

declare @i int
declare @cnt int
declare @currday varchar(20)
set @i = 1
set @cnt = 0
set @currday = DATENAME(dw,@date)
while(@i <= @totaldays)
begin
if(@currday = 'Sunday') set @cnt = @cnt + 1
set @currday = DATENAME(dw,dateadd(dd,1,@date))
set @date = dateadd(dd,1,@date)
set @i = @i + 1
end
print 'Total Sunday in give month are : ' + cast(@cnt as varchar)

Output will be...


Given Year : 2008
Given Month : 6
Total days : 30
First day is : Sunday
Total Sunday in give month are : 5