Return WeekNo (ISO 8601 standard)

,

In MS SQL, January 1 of any year defines the starting number for the week. The DATEPART(wk, 'Jan 1, xxxx') always return 1. However, a lot of reports Week No is actually based on ISO 8601 standard, that is January 1 of any year can be Week 53/52 or Week 1. (http://www.merlyn.demon.co.uk/weekinfo.htm#IDC)

The Rule is as follows:
The first Week of a Year is Number 01, which is :-
*defined as being the week which contains the first Thursday of the Calendar year; which implies that it is also :-
*the first week which is mostly within the Calendar year,
*the week containing January 4th,
*the week starting with the Monday nearest to January 1st.

The conditions are mutually equivalent. See Markus Kuhn and R.H. van Gent, via datefmts.htm, for example.
The last Week of a Year, Number 52 or 53, therefore is :-
*the week which contains the last Thursday of the Calendar year;
*the last week which is mostly within the Calendar year;
*the week containing December 28th;
*the week ending with the Sunday nearest to December 31st.

Run the scripts below, and test

Declare @tDate Datetime
set @tDate =getdate()
select dbo.udf_DT_ISOWeekNum (@tDate)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DT_ISOWeekNum]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DT_ISOWeekNum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DT_YearStart]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DT_YearStart]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  FUNCTION udf_DT_ISOWeekNum
(@AnyDate   Datetime )
RETURNS int
AS


BEGIN
Declare  @ThisYear   		Int 
	,@PreviousYearStart  	Datetime
	,@ThisYearStart   	Datetime
	,@NextYearStart  	Datetime
	,@YearNum  		Int
	,@ISOWeekNum 		int
	,@str			varchar(25)

Set @ThisYear = Year(@AnyDate)
Set @ThisYearStart = dbo.udf_DT_YearStart(@ThisYear)
Set @PreviousYearStart = dbo.udf_DT_YearStart(@ThisYear - 1)
Set @NextYearStart = dbo.udf_DT_YearStart(@ThisYear + 1)


If @AnyDate >= @NextYearStart  
	Begin
		set @ISOWeekNum = Datediff(d,@NextYearStart, @AnyDate)/7 + 1
		set @YearNum = Year(@AnyDate) + 1
	End
If  @AnyDate  < @ThisYearStart  
	begin
		set  @ISOWeekNum = Datediff(d, @PreviousYearStart,@AnyDate)/7 + 1
		set  @YearNum = Year(@AnyDate) - 1
	end
    Else
	begin
	       set @ISOWeekNum = Datediff(d,@ThisYearStart,@AnyDate)/7 + 1
	       set @YearNum = Year(@AnyDate)
	End 

Return @ISOWeekNum 

return @str

End






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE Function udf_DT_YearStart(@Year As Integer) 
RETURNS DATETIME
AS
BEGIN

Declare  @WeekDay As Integer
	,@NewYear As DateTIME
	,@YearStart as datetime 

set @NewYear = Cast((Cast(@Year as varchar(4))+'-01-01') as datetime)
set @WeekDay =  (datepart(dw, dateadd(d, -2 , @NewYear)))%7

If @WeekDay < 4 
    set @YearStart = dateadd(d,  - @WeekDay, @NewYear)
Else
    set @YearStart = dateadd(d,  7- @WeekDay , @NewYear)

return @YearStart

End 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate