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

image

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

image


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

9 comments:

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.

Excellent!

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 (http://msdn.microsoft.com/en-us/library/ms181598.aspx) but the DATEDIFF does not consider the DATEFIRST setting.

Having said that, check Itzik Ben-Gan's query over here
http://www.eggheadcafe.com/software/aspnet/29953401/weekly-totals.aspx

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

Bravo~

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

HI

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

TX
nagaraj

Ratheesh T said...

Hi
I have modified the query to a function as below.

I want to give a date value as input
I want to know the number of the week in which the input date is falling in.
Eg. 2016-05-20 should give 3 as the result, Please help..


/*
FUNCTION fWeeksofMonthCalc
PURPOSE Calculate the number of weeks of a month
ARGUEMENTS @inpdate
AUTHOR ratheesh.t@gmail.com

CALLING SAMPLE
DECLARE @NoWeeks SMALLINT
SELECT @NoWeeks = dbo.fWeeksofMonthCalc ( '2016-05-20' )
SELECT @NoWeeks

--DROP FUNCTION dbo.fWeeksofMonthCalc
*/

CREATE FUNCTION dbo.fWeeksofMonthCalc (@inpdate DATETIME )
RETURNS SMALLINT
AS

BEGIN
DECLARE @Yrinpdate SMALLINT
DECLARE @MnthInpdate SMALLINT
DECLARE @OutNoWeeks SMALLINT

--SET @inpdate = '2016-05-20'
SET @Yrinpdate = DATEPART(YEAR,@inpdate)
SET @MnthInpdate = DATEPART(MONTH,@inpdate)

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

RETURN @OutNoWeeks
END



Thanks,
Ratheesh