根据某个时间点,计算出是当前月的第几周,这里是按照7天为一周计算
如果有更好的方式,欢迎沟通,学习 !!!
select [dbo].[WeekOfMonth]('2023-09-07')
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
GO