两个关于周的sql自定义函数

两个关于周的sql自定义函数

2009年10月27日 星期二 下午 03:59

--得到指定日期本年对应的周序数，第一周为本年内以周日开始周 周日为一周的第一天
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