File:. wmv; Size: 19,535kb; Length: 00:26:39.
Download Address: Sqlfunction_udf_week.rar
The following is updated on 20120105:
2012 just arrived, the software developed uses this function to discover bugs. The first week of 2012 should be 2012-01-01 to 2012-01-07, but this function treats 2012-01-08 to 2012-01-14 as the first week.
Modify this function as follows for this bug:
Copy Code code as follows:
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
