Until Microsoft has fixed all date function to be DATE compliant, try thisCREATE FUNCTION dbo.fnISOWEEK
(
@Date DATE
)
RETURNS TINYINT
AS
BEGIN
RETURN(
SELECTCASE
WHEN nextYear <= theDate THEN 0
WHEN currYear <= theDate THEN (theDate - currYear) / 7
ELSE (theDate - prevYear) / 7
END + 1
FROM(
SELECT(CASE
WHEN prevYear % 400 = 0 THEN -366
WHEN prevYear % 100 = 0 THEN -365
WHEN prevYear % 4 = 0 THEN -366
ELSE -365
END + theDate - theDoY + 4) / 7 * 7 AS prevYear,
(theDate - theDoY + 4) / 7 * 7 AS currYear,
(CASE
WHEN currYear % 400 = 0 THEN 366
WHEN currYear % 100 = 0 THEN 365
WHEN currYear % 4 = 0 THEN 366
ELSE 365
END + theDate - theDoY + 4) / 7 * 7 AS nextYear,
theDate
FROM(
SELECTDATEPART(YEAR, @Date) - 1 AS prevYear,
DATEPART(YEAR, @Date) AS currYear,
DATEDIFF(DAY, '00010101', @Date) AS theDate,
DATEPART(DAYOFYEAR, @Date) AS theDoY
) AS d
) AS d
)
END
N 56°04'39.16"
E 12°55'05.25"