CREATE function GetWeekIndexFirstDate ( @date datetime ) returns int as begin declare @index int; d
CREATE function GetWeekIndexFirstDate
(
@date datetime
)
returns int
as
begin
declare @index int;
declare @firstDate datetime;--当年的1月1号
declare @firstSunday datetime;--当年的第一个周日
declare @firstWeekSunday datetime;--当年的第一周的周日
set @firstDate = convert(datetime,convert(varchar(4),Year(@date)) + "-1-1")
set @firstSunday = case when datepart(dw,@firstDate)=1 then @firstDate else dateadd(dd,8-datepart(dw,@firstDate),@firstDate) end;
if(@date<=@firstSunday)
set @index = 1;
else
set @index = ceiling(datediff(dd,@firstSunday,@date)/7.0)+1
return @index;
end
CREATE function GetWeekIndexFirstMonday
(
@date datetime
)
returns int
as
begin
declare @index int;
declare @firstDate datetime;--当年的1月1号
declare @firstMonday datetime;--当年的第一个周一
declare @lastYearFirstDate datetime;--上一年的1月1号
declare @lastYearFirstMonday datetime;--上一年的第一个周一
select @firstDate = convert(datetime,convert(varchar(4),Year(@date)) + "-1-1")
, @lastYearFirstDate = convert(datetime,convert(varchar(4),datepart(yyyy,@date)-1) + "-1-1")
select @firstMonday = case when datepart(dw,@firstDate)<=2 then dateadd(dd,2-datepart(dw,@firstDate) ,@firstDate)
else dateadd(dd,9-datepart(dw,@firstDate),@firstDate) end
,@lastYearFirstMonday = case when datepart(dw,@lastYearFirstDate)<=2 then dateadd(dd,2-datepart(dw,@lastYearFirstDate) ,@lastYearFirstDate)
else dateadd(dd,9-datepart(dw,@lastYearFirstDate),@lastYearFirstDate) end;
if(@date >=@firstMonday)
set @index = floor(datediff(dd,@firstMonday,@date)/7.0)+1;
else
set @index = floor(datediff(dd,@lastYearFirstMonday,@date)/7.0)+1
return @index;
end
--结束END--
本文标题: sql某个日期是当年的第几周
本文链接: https://www.lsjlt.com/news/5931.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0