Extended ISO week function
CREATE FUNCTION dbo.fnISOWEEK
(
@theDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
RETURN (
SELECT CASE
WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7
WHEN @theDate >= '99990101' THEN 52
WHEN NextYear <= @theDate THEN 0
WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7
ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7
END + 1
FROM (
SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,
DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
FROM (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4
) AS x
) AS d
)
END