If this is an MSSQL issue (admin settings or locale) or whatever, I'm having unsaid. The same issue is with the dayofyear() function aswell...
I have no option to test the week functions i MySQL (that i hold more dear), so you reading, please do test around so I have something to read when I get home from work.
Just thought I'd mention it passing by as someone might find it of interest.
Code: Select all
create function Week_IsoWeek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @Week_IsoWeek int
SET @Week_IsoWeek= DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@Week_IsoWeek=0)
SET @Week_IsoWeek=dbo.Week_IsoWeek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @Week_IsoWeek=1
RETURN(@Week_IsoWeek)
END