How to get week number of the month from the date in sql server 2008

In SQL Statement in microsoft sql server, there is a built-in function to get week number but it is the week of the year.

Select DatePart(week, '2012/11/30') // **returns 48**

The returned value 48 is the week number of the year.

Instead of 48, I want to get 1, 2, 3 or 4 (week number of the month). I think the week number of the month can be achieved by modules with Month Number of this week. For e.g.

Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')

But I want to know is there other built-in functions to get WeekNumber of the month in MS SQL SERVER.

Solution 1:

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

DECLARE @date date = '2012-09-01'
SELECT (day(datediff(d,0,@date)/7*7)-1)/7+1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

DECLARE @date date = '2012-09-01'
SELECT 
  datediff(ww,datediff(d,0,dateadd(m,datediff(m,7,@date),0)
    )/7*7,dateadd(d,-1,@date))+1

I received an email from Gerald. He pointed out a flaw in the second method. This should be fixed now

I received an email from Ben Wilkins. He pointed out a flaw in the first method. This should be fixed now

Solution 2:

DECLARE @DATE DATETIME
SET @DATE = '2013-08-04'

SELECT DATEPART(WEEK, @DATE)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0))+ 1 AS WEEK_OF_MONTH

Solution 3:

No built-in function. It depends what you mean by week of month. You might mean whether it's in the first 7 days (week 1), the second 7 days (week 2), etc. In that case it would just be

(DATEPART(day,@Date)-1)/7 + 1

If you want to use the same week numbering as is used with DATEPART(week,), you could use the difference between the week numbers of the first of the month and the date in question (+1):

(DATEPART(week,@Date)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) + 1

Or, you might need something else, depending on what you mean by the week number.

Solution 4:

Just look at the date and see what range it falls in.

Range 1-7 is the 1st week, Range 8-14 is the 2nd week, etc.

SELECT 
CASE WHEN DATEPART(day,yourdate) < 8 THEN '1' 
  ELSE CASE WHEN DATEPART(day,yourdate) < 15 then '2' 
    ELSE CASE WHEN  DATEPART(day,yourdate) < 22 then '3' 
      ELSE CASE WHEN  DATEPART(day,yourdate) < 29 then '4'     
        ELSE '5'
      END
    END
  END
END