Find Number of Weeks in a Month using SQL Serv

I have been asked this question plenty of times – How do I calculate the number of weeks in a month. The answer to this query depends on how do you define a week. For simplicity purposes, I will take a week from Sunday to Saturday as depicted in the calendar



So November has 5 weeks!

Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement

DECLARE @Yr SMALLINT
SET @Yr = 2009

;WITH NumWeeks
AS
(
SELECT Number + 1 as mth,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7 AS lst
FROM master..spt_values
WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;
OUTPUT