有没有本月次周的计算函数？

Question

create function udf_WeekOfMonth(@Date datetime)
-- 返回 @Date 是所在月的第几周 周日是当周的最后一天
returns integer
as
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1
                           then dateadd(month,datediff(month,0,@Date),0) - 1
                      else
                           dateadd(month,datediff(month,0,@Date),0)
                      end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then @Date-1
                      else @Date
                 end
               ) + 1
endgoselect dbo.udf_WeekOfMonth('2005-12-02')
select dbo.udf_WeekOfMonth(getdate())

Answer 1

select convert(int,datename(week,getdate()))-convert(int,datename(week,dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)))
)

Answer 2

Select 本年第几周=datename(week,'2006-07-01'),本日星期几=datename(weekday,'2006-07-01')
虽然SQL没有计算本月第几周的函数,但是利用DateName这个函数还是可以计算出来的

Answer 3

例子:
比如我们计算2006-07-25是本月的第几周
select cast(datename(week,'2006-07-25') as int)-cast(datename(week,'2006-07-01') as int)因为此函数类型不是数值型,所以需要转换类型