blocks|key|252262|text|没有内置的函数来为您获取周号。我认为分割对你没有任何帮助，因为一个月的周数不是固定不变的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252263|http://msdn.microsoft.com/en-us/library/bb675168.aspx|offset|length|252264|我想你可以将数字(48)除以4，并将相同的模块作为这个月的周数进行预测，方法是在结果中添加一个。|252265|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1H|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

There is no inbuilt function to get you the week number. I dont think dividing will help you anyway as the number of weeks in a month is not constant.

<a href="http://msdn.microsoft.com/en-us/library/bb675168.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/bb675168.aspx</a>

I guess you can divide the number(48) by 4 and take the modules of the same and project that as the week number of that month, by adding one to the result.

blocks|key|252291|text|没有内置功能。这取决于你每月每周的意思。你可能指的是前七天(第一周)，第二天(第二周)，等等。在这种情况下，只是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252292|(DATEPART(day,@Date)-1)/7+%2B+1|offset|length|style|CODE|252293|如果要使用与DATEPART(+week，)相同的周编号，则可以使用月份第一个星期数与所述日期(%2B1)之间的差异：|252294|(DATEPART(week,@Date)-+DATEPART(week,DATEADD(m,+DATEDIFF(m,+0,+@Date),+0)))+%2B+1|252295|或者，你可能需要其他的东西，这取决于你所指的周数。|252296|entityMap^0|0|0|T|0|0|0|27|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|O|$]]

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 

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

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): 

<code>(DATEPART(week,@Date)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) + 1</code> 

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

blocks|key|4396190|text|这里有两种不同的方式，两种方式都是假设周从星期一开始|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396191|如果您想要周全，所以它们属于它们开始的月份:因此，周六2012-09-01和星期日2012-09-02是第4周，而2012-09-03是第1周，请使用以下命令：|4396192|DECLARE+@date+date+=+'2012-09-01'
SELECT+(day(datediff(d,0,@date)/7*7)-1)/7%2B1|code-block|syntax|javascript|4396193|如果您的周数减少，那么2012-09-01星期六和2012-09-02星期日是第一周，而2012-09-03是第二周，请使用以下命令：|4396194|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))%2B1|4396195|我收到杰拉尔德的一封电子邮件。他指出了第二种方法的一个缺陷。这个应该现在就解决。|4396196|我收到了本·威尔金斯的一封邮件。他指出了第一种方法的一个缺陷。这个应该现在就解决。|4396197|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:
<pre><code>DECLARE @date date = '2012-09-01'
SELECT (day(datediff(d,0,@date)/7*7)-1)/7+1
</code></pre>
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:
<pre><code>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
</code></pre>
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

blocks|key|4396199|text|下面是一个关于一个月内一周的第一天和最后一天的建议：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396200|--+Build+a+temp+table+with+all+the+dates+of+the+month+
drop+table+#tmp_datesforMonth+
go

declare+@begDate+datetime
declare+@endDate+datetime

set+@begDate+=+'6/1/13'
set+@endDate+=+'6/30/13';

WITH+N(n)+AS++
(+++SELECT+0++
++++++++UNION+ALL+
++++SELECT+n%2B1+
++++FROM+N+
++++WHERE+n+<=+datepart(dd,@enddate)
)
SELECT++++++DATEADD(dd,n,@BegDate)+as+dDate+
into+#tmp_datesforMonth
FROM++++++++N
WHERE+++++++MONTH(DATEADD(dd,n,@BegDate))+=+MONTH(@BegDate)

---+pull+results+showing+the+weeks'+dates+and+the+week+#+for+the+month+(not+the+week+#+for+the+current+month)+

select++MIN(dDate)+as+BegOfWeek
,+MAX(dDate)+as+EndOfWeek+
,+datediff(week,+dateadd(week,+datediff(week,+0,+dateadd(month,+datediff(month,+0,+dDate),+0)),+0),+dDate)+as+WeekNumForMonth+
from+#tmp_datesforMonth
group+by+datediff(week,+dateadd(week,+datediff(week,+0,+dateadd(month,+datediff(month,+0,+dDate),+0)),+0),+dDate)+
order+by+3,+1|code-block|syntax|javascript|4396201|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here's a suggestion for getting the first and last days of the week for a month: 

<pre><code>-- Build a temp table with all the dates of the month 
drop table #tmp_datesforMonth 
go

declare @begDate datetime
declare @endDate datetime

set @begDate = '6/1/13'
set @endDate = '6/30/13';

WITH N(n) AS 
( SELECT 0 
 UNION ALL 
 SELECT n+1 
 FROM N 
 WHERE n &lt;= datepart(dd,@enddate)
)
SELECT DATEADD(dd,n,@BegDate) as dDate 
into #tmp_datesforMonth
FROM N
WHERE MONTH(DATEADD(dd,n,@BegDate)) = MONTH(@BegDate)

--- pull results showing the weeks' dates and the week # for the month (not the week # for the current month) 

select MIN(dDate) as BegOfWeek
, MAX(dDate) as EndOfWeek 
, datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) as WeekNumForMonth 
from #tmp_datesforMonth
group by datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) 
order by 3, 1
</code></pre>

blocks|key|252314|text|DECLARE+@DATE+DATETIME
SET+@DATE+=+'2013-08-04'

SELECT+DATEPART(WEEK,+@DATE)++-
++++DATEPART(WEEK,+DATEADD(MM,+DATEDIFF(MM,0,@DATE),+0))%2B+1+AS+WEEK_OF_MONTH|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|252315|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|4375619|text|看看日期，看看它在什么范围内。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4375620|范围1-7是第一周，范围8-14是第二周，等等。|4375621|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|code-block|syntax|javascript|4375622|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>SELECT 
CASE WHEN DATEPART(day,yourdate) &lt; 8 THEN '1' 
 ELSE CASE WHEN DATEPART(day,yourdate) &lt; 15 then '2' 
 ELSE CASE WHEN DATEPART(day,yourdate) &lt; 22 then '3' 
 ELSE CASE WHEN DATEPART(day,yourdate) &lt; 29 then '4' 
 ELSE '5'
 END
 END
 END
END
</code></pre>

blocks|key|4375629|text|看看这个..。工作很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4375630|declare+@date+as+datetime+=+'2014-03-10'
select+DATEPART(week,@date)+-+DATEPART(week,cast(cast(year(@date)+as+varchar(4))%2B'-'+%2B+cast(month(@date)+as+varchar(2))+%2B+'-01'+as+datetime))%2B1|code-block|syntax|javascript|4375631|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Check this out... its working fine.

<pre><code>declare @date as datetime = '2014-03-10'
select DATEPART(week,@date) - DATEPART(week,cast(cast(year(@date) as varchar(4))+'-' + cast(month(@date) as varchar(2)) + '-01' as datetime))+1
</code></pre>

blocks|key|4396295|text|使用Dense_Rank函数的脏但容易的一行。业绩将受到影响，但仍然有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396296|DENSE_RANK()over(Partition+by+Month(yourdate),Year(yourdate)+Order+by+Datepart(week,yourdate)+asc)+as+Week|code-block|syntax|javascript|4396297|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A dirty but easy one liner using Dense_Rank function. Performance WILL suffer, but effective none the less.

<pre><code>DENSE_RANK()over(Partition by Month(yourdate),Year(yourdate) Order by Datepart(week,yourdate) asc) as Week
</code></pre>

blocks|key|252337|text|类似于第二种解决方案，减少代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252338|declare+@date+datetime+=+'2014-03-31'
SELECT+DATEDIFF(week,0,@date)+-+(DATEDIFF(week,0,DATEADD(dd,+-DAY(@date)%2B1,+@date))-1)|code-block|syntax|javascript|252339|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Similar to the second solution, less code:

<pre><code>declare @date datetime = '2014-03-31'
SELECT DATEDIFF(week,0,@date) - (DATEDIFF(week,0,DATEADD(dd, -DAY(@date)+1, @date))-1)
</code></pre>

blocks|key|4375661|text|floor((day(@DateValue)-1)/7)%2B1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4375662|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>floor((day(@DateValue)-1)/7)+1
</code></pre>

blocks|key|4396353|text|给你..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396354|我正在使用下面的代码。|4396355|DATEPART(WK,@DATE_INSERT)+-+DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm,+DATEDIFF(m,0,@DATE_INSERT),0))))+%2B+1|code-block|syntax|javascript|4396356|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here you go....

Im using the code below..

<pre><code>DATEPART(WK,@DATE_INSERT) - DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DATE_INSERT),0)))) + 1
</code></pre>

blocks|key|4375793|text|这是一个查询，它在每周的startday和endday上都会显示周号。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4375794|SET+DATEFIRST+2++++

DECLARE+@FROMDATE+DATE='12-JAN-2015'
--+Get+the+first+day+of+month
DECLARE+@ALLDATE+DATE=DATEADD(month,+DATEDIFF(month,+0,+@FROMDATE),+0)
DECLARE+@FIRSTDATE+DATE


;WITH++CTE+as
(
+++++--+Get+all+dates+in+that+month
+++++SELECT+1+RNO,CAST(@ALLDATE+AS+DATE)+as+DATES+
+++++UNION+ALL
+++++SELECT+RNO%2B1,+DATEADD(DAY,1,DATES+)
+++++FROM++++CTE
+++++WHERE+++DATES+<+DATEADD(MONTH,1,@ALLDATE)
)
--+Retrieves+the+first+day+of+week,+ie,+if+first+day+of+week+is+Tuesday,+it+selects+first+Tuesday+
SELECT+TOP+1+@FIRSTDATE+=+++DATES+
FROM++++CTE+
WHERE+DATEPART(W,DATES)=1

SELECT+(DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)%2B1+WEEKNO|code-block|syntax|javascript|4375795|关于更多的信息，我已经回答了以下问题。可以检查一下。|4375796|How+do+I+find+week+number+of+a+date+according+to+DATEFIRST|unordered-list-item|4375797|4375798|4375799|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/27700839/how-do-i-find-week-number-of-a-date-according-to-datefirst^0|C|8|L|6|0|0|0|0|1M|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|15|8|@]|D|@]|E|$]]|$1|M|3|N|5|O|7|16|8|@]|D|@$9|17|A|18|1|19]]|E|$]]|$1|P|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|S|$T|$5|U|V|W|E|$X|Y]]]]

Here is the query that brings the week number on whatever the <code>startday</code> and <code>endday</code> of the week it may be.

<pre><code>SET DATEFIRST 2 

DECLARE @FROMDATE DATE='12-JAN-2015'
-- Get the first day of month
DECLARE @ALLDATE DATE=DATEADD(month, DATEDIFF(month, 0, @FROMDATE), 0)
DECLARE @FIRSTDATE DATE


;WITH CTE as
(
 -- Get all dates in that month
 SELECT 1 RNO,CAST(@ALLDATE AS DATE) as DATES 
 UNION ALL
 SELECT RNO+1, DATEADD(DAY,1,DATES )
 FROM CTE
 WHERE DATES &lt; DATEADD(MONTH,1,@ALLDATE)
)
-- Retrieves the first day of week, ie, if first day of week is Tuesday, it selects first Tuesday 
SELECT TOP 1 @FIRSTDATE = DATES 
FROM CTE 
WHERE DATEPART(W,DATES)=1

SELECT (DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)+1 WEEKNO
</code></pre>

For more information I have answered for the below question. Can check that.

<ul>
<li><a href="https://stackoverflow.com/questions/27700839/how-do-i-find-week-number-of-a-date-according-to-datefirst">How do I find week number of a date according to DATEFIRST</a></li>
</ul>

blocks|key|252396|text|您可以通过获得月的最小周数来获得周数，并从周数中减去它。假设您有一个有日期的表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252397|select
++++emp_id,+dt+,+datepart(wk,dt)+-+(select+min(datepart(wk,dt))
from
++++workdates+)+%2B+1+from+workdates|code-block|syntax|javascript|252398|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can simply get week number by getting minimum week number of month and deduct it from week number. Suppose you have a table with dates

<pre><code>select
 emp_id, dt , datepart(wk,dt) - (select min(datepart(wk,dt))
from
 workdates ) + 1 from workdates
</code></pre>

blocks|key|4396460|text|代码如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396461|set+datefirst+7
declare+@dt+datetime='29/04/2016+00:00:00'
select+(day(@dt)%2Bdatepart(WEEKDAY,dateadd(d,-day(@dt),@dt%2B1)))/7|code-block|syntax|javascript|4396462|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Code is below:

<pre><code>set datefirst 7
declare @dt datetime='29/04/2016 00:00:00'
select (day(@dt)+datepart(WEEKDAY,dateadd(d,-day(@dt),@dt+1)))/7
</code></pre>

blocks|key|4396491|text|试试下面的代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396492|declare+@dt+datetime='2018-03-15+05:16:00.000'
IF+(Select+(DatePart(DAY,@dt)%257))>0
++Select++(DatePart(DAY,@dt)/7)+%2B1
ELSE
++Select++(DatePart(DAY,@dt)/7)|code-block|syntax|javascript|4396493|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try Below Code:

<pre><code>declare @dt datetime='2018-03-15 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))&gt;0
 Select (DatePart(DAY,@dt)/7) +1
ELSE
 Select (DatePart(DAY,@dt)/7)
</code></pre>

blocks|key|4375848|text|解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4375849|declare+@dt+datetime='2018-03-31+05:16:00.000'
IF+(Select+(DatePart(DAY,@dt)%257))>0
Select++(DatePart(DAY,@dt)/7)+%2B1
ELSE
Select++(DatePart(DAY,@dt)/7)|code-block|syntax|javascript|4375850|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Solution:

<pre><code>declare @dt datetime='2018-03-31 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))&gt;0
Select (DatePart(DAY,@dt)/7) +1
ELSE
Select (DatePart(DAY,@dt)/7)
</code></pre>

blocks|key|4375887|text|以下是在任何情况下对这个查询进行了尝试和测试的解决方案--比如，如果这个月的第一天是星期五，那么这个解决方案也会有效-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4375888|select+(DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))%2B1|code-block|syntax|javascript|4375889|以上是一些解决方案，如果这个月的第一天是星期五，那么第四天是这个月的第二周，这些解决方案就会失败。|4375890|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is the tried and tested solution for this query in any situation - like if 1st of the month is on Friday , then also this will work - 

<pre><code>select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1
</code></pre>

above are some solutions which will fail if the month's first date is on Friday , then 4th will be 2nd week of the month

blocks|key|4396539|text|这里的逻辑也适用于每个月4.3周。从每个月的DATEPART(一周)，除了一月。只是另一种看待事物的方式。这也说明了有第五周的月份。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396540|DECLARE+@date+VARCHAR(10)
SET+@date+=+'7/27/2019'

SELECT+CEILING(DATEPART(WEEK,@date)-((DATEPART(MONTH,@date)-1)*4.3333))+'Week+of+Month'|code-block|syntax|javascript|4396541|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Logic here works as well 4.3 weeks in every month. Take that from the DATEPART(WEEK) on every month but January. Just another way of looking at things. This would also account for months where there is a 5th week

<pre><code>DECLARE @date VARCHAR(10)
SET @date = '7/27/2019'

SELECT CEILING(DATEPART(WEEK,@date)-((DATEPART(MONTH,@date)-1)*4.3333)) 'Week of Month'
</code></pre>

blocks|key|4375909|text|WeekMonth+=+CASE+WHEN+(DATEPART(day,TestDate)+-+datepart(dw,TestDate))>=+22+THEN+'5'+
+++++++++++++++++WHEN+(DATEPART(day,TestDate)+-+datepart(dw,TestDate))>=+15+THEN+'4'+
+++++++++++++++++WHEN+(DATEPART(day,TestDate)+-+datepart(dw,TestDate))>=+8+THEN+'3'
+++++++++++++++++WHEN+(DATEPART(day,TestDate)+-+datepart(dw,TestDate))>=+1+THEN+'2'+
+++++++++++++++++ELSE+'1'
+++++++++++++END+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4375910|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>WeekMonth = CASE WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))&gt;= 22 THEN '5' 
 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))&gt;= 15 THEN '4' 
 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))&gt;= 8 THEN '3'
 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))&gt;= 1 THEN '2' 
 ELSE '1'
 END 
</code></pre>

blocks|key|4375925|text|有一个内置的选项可以获得年的周数|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4375926|**select+datepart(week,getdate())**|code-block|syntax|javascript|4375927|entityMap^0|C|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

There is an inbuilt option to get the week number of the year

<pre><code>**select datepart(week,getdate())**
</code></pre>

blocks|key|253469|text|select+@DateCreated,+DATEDIFF(WEEK,+@DateCreated,+GETDATE())|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|253470|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select @DateCreated, DATEDIFF(WEEK, @DateCreated, GETDATE())
</code></pre>

blocks|key|4396620|text|只有在选择列表中每个月的每周都有表示的情况下，下面才能工作。否则，秩函数将无法工作，但这是一个很好的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396621|SELECT+DENSE_RANK()+OVER+(PARTITION+BY+MONTH(DATEFIELD)+
++++++++++++++++++++++++++ORDER+BY+DATEPART(WEEK,DATEFIELD)+ASC)+AS+WeekofMont|code-block|syntax|javascript|4396622|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Below will only work if you have every week of the month represented in the select list. Else the rank function will not work, but it is a good solution.
<pre><code>SELECT DENSE_RANK() OVER (PARTITION BY MONTH(DATEFIELD) 
 ORDER BY DATEPART(WEEK,DATEFIELD) ASC) AS WeekofMont
</code></pre>

blocks|key|4396636|text|试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4396637|declare+@date+datetime+=+'20210928'

select+convert(int,(((cast(datepart(day,@date)+as+decimal(4,2))/7)-(1.00/7.00))%2B1.00))|code-block|syntax|javascript|4396638|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try this one
<pre><code>declare @date datetime = '20210928'

select convert(int,(((cast(datepart(day,@date) as decimal(4,2))/7)-(1.00/7.00))+1.00))
</code></pre>

blocks|key|4376002|text|select+datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))%2B1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4376003|或|unstyled|4376004|select+datepart(week,@date)-datepart(week,dateadd(d,-datepart(d,@date)%2B1,@date))%2B1|4376005|步骤：|4376006|1，每月的第一天|4376007|年月日的第二周-月份第一天的年份的每周|4376008|3+%2B1|4376009|2023-1-1是周日|4376010|SET+DATEFIRST+1;
DECLARE+@date+date+=+'2023-1-02';
select+datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))%2B1|4376011|回报2|4376012|SET+DATEFIRST+7;
DECLARE+@date+date+=+'2023-1-02';
select+datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))%2B1|4376013|回报1|4376014|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|19|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$B|C]]|$1|U|3|V|5|F|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$B|C]]|$1|Y|3|Z|5|F|7|1D|8|@]|9|@]|A|$]]|$1|10|3|-4|5|F|7|1E|8|@]|9|@]|A|$]]]|11|$]]

<pre><code>select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1
</code></pre>
or
<pre><code>select datepart(week,@date)-datepart(week,dateadd(d,-datepart(d,@date)+1,@date))+1
</code></pre>
steps: 
1,get the first day of month 
2,week of year of the date - week of year of the first day of the month 
3,+1
2023-1-1 is sunday
<pre><code>SET DATEFIRST 1;
DECLARE @date date = '2023-1-02';
select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1
</code></pre>
return 2
<pre><code>SET DATEFIRST 7;
DECLARE @date date = '2023-1-02';
select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1
</code></pre>
return 1

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.

<pre><code>Select DatePart(week, '2012/11/30') // **returns 48**
</code></pre>

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.

<pre><code>Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')
</code></pre>

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

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在microsoft服务器中的SQL语句中，有一个内置函数来获取周号，但它是一年中的一周。Select DatePart(week, '2012/11/30') // **returns 48**返回值48是一年中的周数。我想得到的不是48，而是1、2、3或4 (月周数)。我认为这个月的周数可以通过这个星期的月数模块来...

问如何从sql server 2008中的日期开始获取月份的周数
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从sql server 2008中的日期开始获取月份的周数EN