转：SQL Server：获取当前日期是本月的第几周

原文转载链接找不着了。

以下问转载内容：

第一种：

 

--获取当前日期是本月的第几周
--测试：select dbo.getMonthWeek(getdate()) 结果:10月的第2周
CREATE function [dbo].[getMonthWeek](@d datetime)
returns varchar(20)
as
begin
declare @returns varchar(20),
              @monthfirstDay datetime,
              @firstMondy datetime

select @monthfirstDay=left(convert(varchar,@d,23),7)+'-01'

if not exists(select 1 
                     from master.dbo.spt_values
                     where type=N'P' and number between 0 and datediff(d,@monthfirstDay,@d)
                     and datepart(dw,dateadd(d,number,@monthfirstDay))=2)
begin
  select @monthfirstDay=dateadd(mm,-1,@monthfirstDay)
end

  ;with t as(select 'days'=dateadd(d,number,@monthfirstDay)
                   from master.dbo.spt_values
                   where type=N'P' and number<=7)
   select @firstMondy=min([days]) 
    from t
    where datepart(dw,[days])=2

select @returns=rtrim(datepart(mm,@monthfirstDay))+'月的第'+rtrim(datediff(d,@firstMondy,@d)/7+1)+'周'

return @returns
end

第二种：

 

 

--获取当前日期是本月的第几周
--测试：select dbo.WeekOfMonth(getDate());结果:2
CREATE   FUNCTION   [dbo].[WeekOfMonth](@day datetime)     
RETURNS int  
AS   
begin  
   
----declare @day datetime   
declare @num int  
declare @Start datetime  
declare @dd int  
declare @dayofweek char(8)  
declare @dayofweek_num char(8)  
declare @startWeekDays int  
---set @day='2009-07-05'   
if datepart(dd,@day)=1  
return 1  
else  
set @Start= (SELECT   DATEADD(mm,   DATEDIFF(mm,0,@day),   0)) --一个月第一天的   
set @dayofweek= (datename(weekday,@Start)) ---得到本月第一天是周几   
set @dayofweek_num=(select (case @dayofweek when '星期一' then 2  
when '星期二' then 3  
when '星期三' then 4  
when '星期四' then 5  
when '星期五' then 6  
when '星期六' then 7  
when '星期日' then 1  
end))  
set @dayofweek_num= 7-@dayofweek_num+1 ---得到本月的第一周一共有几天   
---print @dayofweek_num   
   set @dd=datepart(dd,@day) ----得到今天是这个月的第几天   
--print @dd   
if @dd<=@dayofweek_num --小于前一周的天数   
return 1  
else   
set @dd=@dd-@dayofweek_num  
if @dd % 7=0  
     begin  
       set @num=@dd / 7  
        return @num+1  
          
     end  
   else --if @dd % 7<>0   
      
     set @num=@dd / 7  
set @num=@num+1+1  
       return @num  
end