在sql Server自定义一个用户定义星期函数(函数自定义)

下载地址：SqlFunction_udf_Week.rar
以下内容于20120105更新：
2012年刚到，开发的软件使用这个函数发现bug。2012年第一周应该是2012-01-01至2012-01-07，但此函数却把2012-01-08至2012-01-14当作第一周。就此bug对此函数修改如下：

复制代码代码如下:

 
SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

ALTER FUNCTION [dbo].[udf_Week] 

( 

@StartYear INT, 

@EndYear INT 

) 

RETURNS @Week TABLE([WeekId] INT IDENTITY(1,1) PRIMARY KEY,[Year] [INT] NULL,[Week] [INT] NULL,[StartDate] [DATETIME] NULL,[EndDate] [DATETIME] NULL) 

AS 

BEGIN 

DECLARE @YearStartDate DATETIME 

DECLARE @YearEndDate DATETIME 

DECLARE @WeekStartDate DATETIME 

DECLARE @WeekEndDate DATETIME 

DECLARE @Weeks INT 
WHILE @StartYear <= @EndYear 

BEGIN 

SET @YearStartDate = CAST((CAST(@StartYear AS VARCHAR(4)) + '-01-01') AS DATETIME) 

SET @YearEndDate= CAST((CAST(@StartYear AS VARCHAR(4))+ '-12-31') AS DATETIME) 

SET @Weeks = 1 
IF DATEPART(DW,@YearStartDate) > 4 

SET @YearStartDate = DATEADD(DAY,(8 - DATEPART(DW,@YearStartDate)) ,@YearStartDate) 

ELSE 

SET @YearStartDate = DATEADD(DAY,(-(DATEPART(DW,@YearStartDate)-1)),@YearStartDate) 
SET @WeekStartDate = @YearStartDate 

SET @WeekEndDate = DATEADD(DAY,6,@WeekStartDate) 

WHILE DATEDIFF(DAY,@WeekStartDate,@YearEndDate) >= 4 

BEGIN 

INSERT INTO @Week([Year],[Week],[StartDate],[EndDate]) VALUES (@StartYear,@Weeks,@WeekStartDate,@WeekEndDate) 

SET @Weeks = @Weeks + 1 

SET @WeekStartDate = @WeekStartDate + 7 

SET @WeekEndDate = @WeekEndDate + 7 

END 

SET @StartYear = @StartYear + 1 

END 

RETURN 

END