--得到指定日期本年对应的周序数,第一周为本年内以周日开始周 周日为一周的第一天
CREATE function WeekOfDay(@Date nvarchar(10))
returns nvarchar(7)
as
begin
declare @DWofYearsDay tinyint,@WeekOfDay tinyint,@Year smallint
set @Year=year(@Date)
set @DWofYearsDay=DATEPART(dw,cast(@Year as nvarchar)+'0101')
if @DWofYearsDay=1
set @WeekOfDay= DATEPART(ww,@Date)
else
set @WeekOfDay= DATEPART(ww,@Date)-1
if @WeekOfDay=0
begin
set @Year=@Year-1
set @WeekOfDay=52
end
return cast(@Year as nvarchar)+'-'+cast(@WeekOfDay as nvarchar)
end
--得到指定某年某周的最大日期,第一周为本年内以周日开始周 周日为一周的第一天
CREATE function MaxDayOfWeek(@Week nvarchar(7))
returns smalldatetime
as
begin
declare @DWofYearsDay tinyint,@Weeks tinyint,@Year nvarchar(4),@MaxDayOfWeek smalldatetime,@Days smallint
set @Year=left(@Week,4)
set @Weeks=substring(@Week,6,2)
set @DWofYearsDay=DATEPART(dw,@Year+'0101')
if @DWofYearsDay=1
set @Days= @Weeks*7
else
set @Days= @Weeks*7+(7-@DWofYearsDay)
set @MaxDayOfWeek=DATEADD (d, @Days, @Year+'0101' )
return @MaxDayOfWeek
end