Return WeekNo (ISO 8601 standard)

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
,@strvarchar(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