Find Number of Weeks in a Month using SQL Server

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

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: