Week number in a Month
$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
Another way
Shorter and simpler
Thanks.
Very Cool. Thanks for posting
Thanks for posting this...
Re: Very Cool. Thanks for posting
Re: Another way
Thanks...
wow..cool......thanx lot...
One use for this week of the month:
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!
Week starting on Monday
Re: Week starting on Monday
See SET DATEFIRST command and @@DATEFIRST function
It didn't work with "set
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
first day of week
how did you resolved this problem I didn't. @dt values ?
will it works for the date
if so it will be the 6th week for that month.
Yes it's correct
Thanks for the quick
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
Thank you very very much
Sergey! perfect solution!
looking this from last 2
Calculate first and last day of a week in a month
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,
Doesn't work
Re: Doesn't work
Derive Week of Month