return datediff(week,@StartDate,@EndDate) -- + 1 + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1 then 1 else 0 end - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1 then 1 else 0 end end
go
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