Week number in a Month

topic: 


$SAMPLECODE$

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- There's simpler and shorter version in the comments below

DECLARE @dt datetime, @WeekOfMonth tinyint
SET @dt = '2007-07-08'

SET @WeekOfMonth = (DAY(@dt) + 
				(DATEPART(dw, DATEADD (month, DATEDIFF (month, 0, @dt), 0)) 
				  --^-- The day of the week for the first day of month
			-1) -- # of days to add to make the first week full 7 days
		-1)/7 + 1 
PRINT @WeekOfMonth

Comments

Try this

1
2
3
4
5

DECLARE @dt datetime, @WeekOfMonth tinyint
SET @dt = '2007-07-08'
set @WeekOfMonth = datediff(week, dateadd(month, datediff(month, 0, @dt), 0), @dt) +1
PRINT @WeekOfMonth

It's shorter and simpler.

Thanks.

Very Cool.
Thanks for posting this...

Really Helpful. Thank You

Great.... Just the thing I was looking for.
Thanks...

wow..cool......thanx lot...

-- parts of this taken from http://joecasella.wordpress.com/2011/05/10/tsql-tuesday-18-using-a-recursive-cte-to-create-a-calendar-table/#comment-38
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


CREATE PROCEDURE dbo.spGetMonthCalendar
	@Month INT = 1,
	@Year INT = 2010
AS BEGIN

	DECLARE @StartDate DATE
	DECLARE @EndDate DATE

	-- Create the start date value
	SET @StartDate = CONVERT(DATE, RIGHT('0000' + CONVERT(VARCHAR, @Year),4) + '-' + RIGHT('00' + CONVERT(VARCHAR, @Month), 2) + '-01')
	-- Create the end date
	SET @EndDate = DATEADD(M,1,@StartDate)

	;WITH Sales_CTE (MonthDate)
	AS
	(
	Select 
		@StartDate
	UNION ALL
	Select DATEADD(dd, 1, MonthDate) as MonthDate 
		from Sales_CTE 
	WHERE 
		DATEADD(dd, 1, MonthDate) < @EndDate
	)
	select 
		[1] as sun, 
		[2] as mon, 
		[3] as tue, 
		[4] as wed, 
		[5] as thu, 
		[6] as fri, 
		[7] as sat
	FROM
		(
		select 
			day(MonthDate) as MONTHDATE,
			datepart(weekday, MonthDate) AS DAYOFWEEK,
			DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, MonthDate), 0), MonthDate) +1 as ROW
			--row_number() over(order by MonthDate)/7 as ROW 
		from Sales_CTE 
		) as source_table
	pivot (
		sum(monthdate)
		for dayofweek in ([1],[2],[3],[4],[5],[6],[7])
		) as pivottable
END


The results (if ran with default parameters):

<table border="0" style="font-size:10; font-family: Lucida-Console; border-width: 4px; cell-spacing:0px; cell-padding:2px">
<thead><tr>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">sun</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">mon</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">tue</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">wed</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">thu</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">fri</th>
<th style="font-weight:bold;background-color:LightSteelBlue;font-size:14;">sat</th>
</tr></thead>
<tbody>
<tr style="background-color:#DDDDDD;font-size:12">
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>1</td>
<td>2</td>
</tr>
<tr style="background-color:White;font-size:12">
<td>3</td>
<td>4</td>
<td>5</td>
<td>6</td>
<td>7</td>
<td>8</td>
<td>9</td>
</tr>
<tr style="background-color:#DDDDDD;font-size:12">
<td>10</td>
<td>11</td>
<td>12</td>
<td>13</td>
<td>14</td>
<td>15</td>
<td>16</td>
</tr>
<tr style="background-color:White;font-size:12">
<td>17</td>
<td>18</td>
<td>19</td>
<td>20</td>
<td>21</td>
<td>22</td>
<td>23</td>
</tr>
<tr style="background-color:#DDDDDD;font-size:12">
<td>24</td>
<td>25</td>
<td>26</td>
<td>27</td>
<td>28</td>
<td>29</td>
<td>30</td>
</tr>
<tr style="background-color:White;font-size:12">
<td>31</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
</tbody>
</table>

Agreed Very Cool!

this is the best code a found. but i need the week number of a month when the week starts on Monday not on Sunday. can u help? Thanks

Hi Laura,

See SET DATEFIRST command and @@DATEFIRST function

It didn't work with "set datefirst 1" for me. it returned the same result, as the first day of the week was sunday. but i resolved the problem using this function:

WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt - 1) +1

eg: in february 2013:
Mo Tu We Th Fr Sa Su
................ 01 02 03 -- First week
04 05 06 07 08 09 10 -- Second week
11 12 13 14 15 16 17 -- Third week
18 19 20 21 22 23 24 -- Forth week
25 26 27 28 ........... -- Fifth week

This doesn't work for months with 6 weeks, eg. 1st of september will return zero. Set date doesn't seem to work with this eiter :(

Can you help me?
how did you resolved this problem I didn't. @dt values ?

will it work for the date 1/31/2010 ?
if so it will be the 6th week for that month.

Yes it's correct. The 1/31/2010 is Sunday of the 6th week in the month.

Thanks for the quick response.
Then it wont work for me, i need it as a 6th week, So please can you give me a query to make it as 5th week like
jan 1st ---7th 1st week
jan 8th -- 14th 2nd week
jan 15th--21nd 3rd week
jan 22rd--28th 4th week
jan 29th -- what ever comes hear as 5th week
and so on.

I mean jan 1st of 2010 to be as first day to calculate the week number.

It's simple

1
2

SELECT ((DATEPART(day, @dt)-1) / 7) + 1

Thank you very very much sergey

Sergey! perfect solution! I've been looking for this information for 2 days! Thanks again.

looking this from last 2 days..... Thanks.

Hi Sergey ,

I want to calculate first day and last day of a week in a month.For Example,

sep 01 2011 - sep 03 2011,
sep 04 2011 - sep 10 2011,
sep 11 2011 - sep 17 2011 ,
sep 18 2011 - sep 24 2011,
sep 25 2011 - sep 30 2011,

Please check for 6th March 2016... printing as 2nd Week.. But it is first week.

It's 2nd week in calendar terms

The shorter and simpler solution submitted by Anonymous really helped me. Thank you much! I'm using it in a stored procedure, although I'm still trying to figure out how the "Set @WeekofMonth" formula was derived.