November 27, 2009

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

@Yr = 2009

;WITH NumWeeks
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;



About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


Ron said...

I have done this several times, usually by calculation the first day of the month that the week start day occurs and looping through the weeks.


Caven Ling said...

Thanks your solutions.

But i have another problem right now.

The problem is how can i find number of weeks in a month using SQL Server if the week is starting from saturday to friday.

Please teach me. Thanks

Suprotim Agarwal said...

You can set the first day of week using DATEFIRST ( but the DATEDIFF does not consider the DATEFIRST setting.

Having said that, check Itzik Ben-Gan's query over here

Caven Ling said...

Can i ask one more question?

How can i calculate the number of weeks by month, not by year. Which means that maximum weeks is 6.
The first day of week is starting from saturday to friday.

Thanks Suprotim Agarwal.

Suprotim Agarwal said...

Cavin: We are calculating the no. of weeks by month. The year was taken to set a limit on the calculation, for eg: the year 2009. You can always keep a date range if you want to calculate between a couple months in the same year.

Caven Ling said...


Thanks Suprotim Agarwal..

Anonymous said...

You do realize that according your result you have 61 weeks, that means that a year has 427 days ...

nagaraj said...


If we want to achieve same results with two given any dates, how would you write it?