摘要:計算該用份的第幾周
不是自己寫的,在網路上找到的資料,記錄一下
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create function [dbo].[udf_WeekOfMonth](@Date datetime)
-- 返回所在日期是該月份第幾周,星期一為周的第一天
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
end
set QUOTED_IDENTIFIER ON
go
Create function [dbo].[udf_WeekOfMonth](@Date datetime)
-- 返回所在日期是該月份第幾周,星期一為周的第一天
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
end