May 28, 2007 at 12:19 pm
Hi,
I have implemented an isoWeek function for use in my applications. When I use the function on an english XP with SQL2005 everything works fine. When I run the same function on a dutch W2003 with SQL2005 I get stange results. Does anyone have an idea what can cause this behaviour? I think it has something to do with OS settings, but I cannot figure it out.
XP: select dbo.isoweek(convert(datetime,'27-05-2007',105)) ==> 21
W2003: select dbo.isoweek(convert(datetime,'27-05-2007',105)) ==> 9
The function I am using:
ALTER
FUNCTION [dbo].[ISOweek] (@DATE datetime)
--Rekent ISO 8601 weeknummer uit gegeven de datum.
--Aanroep: select dbo.isoweek(CONVERT(DATETIME,'01-01-2005',105));
RETURNS
int
WITH
EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int
SELECT @ISOweek = CASE
-- Exception where <date expression> is part of week 52 (or 53) of the previous year
WHEN @DATE < CASE (DATEPART(dw, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
END
THEN
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@DATE) - 1 AS CHAR(4)) + '-01-04')
END,
@DATE
) / 7) + 1
-- Exception where <date expression> is part of week 1 of the following year
WHEN @DATE >= CASE (DATEPART(dw, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@DATE) + 1 AS CHAR(4)) + '-01-04')
END
THEN 1
ELSE
-- Calculate the ISO week number for all dates that are not part of the exceptions above
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@DATE) AS CHAR(4)) + '-01-04')
END,
@DATE
) / 7) + 1
END
RETURN(@ISOweek)
END
;
Thanks in advance,
Mark
May 29, 2007 at 10:30 pm
Your function depends on the setting of DATEFIRST, and the default setting for DATEFIRST depends on the national language setting of SQL Server.
The function on this link is independent of the DATEFIRST setting, and is much simpler. ISO Week of Year Function:
June 1, 2007 at 5:16 am
Michael,
Thanks for your response, that function did the trick.
Mark
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply