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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

8 Responses to "Find Number of Weeks in a Month using SQL Server"
  1. Ron said...
    November 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!

  2. Caven Ling said...
    December 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

  3. Suprotim Agarwal said...
    December 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

  4. Caven Ling said...
    December 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.

  5. Suprotim Agarwal said...
    December 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.

  6. Caven Ling said...
    December 23, 2010 at 11:49 PM

    Bravo~

    Thanks Suprotim Agarwal..

  7. Anonymous said...
    July 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 ...

  8. nagaraj said...
    December 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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions