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

at

11 comments:

  1. RonNovember 27, 2009 at 4:07 PM

    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!

    ReplyDelete
  2. Caven LingDecember 22, 2010 at 1:00 AM

    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

    ReplyDelete
  3. Suprotim AgarwalDecember 22, 2010 at 2:26 AM

    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

    ReplyDelete
  4. Caven LingDecember 22, 2010 at 7:21 PM

    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.

    ReplyDelete
  5. Suprotim AgarwalDecember 23, 2010 at 4:03 AM

    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.

    ReplyDelete
  6. Caven LingDecember 23, 2010 at 11:49 PM

    Bravo~

    Thanks Suprotim Agarwal..

    ReplyDelete
  7. AnonymousJuly 5, 2012 at 6:02 AM

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

    ReplyDelete
  8. nagarajDecember 10, 2013 at 1:38 AM

    HI

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

    TX
    nagaraj

    ReplyDelete
  9. Ratheesh TMay 20, 2016 at 6:47 AM

    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

    ReplyDelete
  10. UnknownSeptember 12, 2016 at 2:48 AM

    Hi,
    It was wrong.
    Plz Check with 2012 year 'January' Month.. (It has 6 weeks).

    ReplyDelete
  11. UnknownSeptember 12, 2016 at 2:49 AM

    Hi,
    It was wrong.
    Plz Check with 2012 year 'January' Month.. (It has 6 weeks).

    ReplyDelete