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;
About The Author
11 comments:
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!
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
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
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.
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.
Bravo~
Thanks Suprotim Agarwal..
You do realize that according your result you have 61 weeks, that means that a year has 427 days ...
HI
If we want to achieve same results with two given any dates, how would you write it?
TX
nagaraj
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
Hi,
It was wrong.
Plz Check with 2012 year 'January' Month.. (It has 6 weeks).
Hi,
It was wrong.
Plz Check with 2012 year 'January' Month.. (It has 6 weeks).
Post a Comment