MSSQL issue using datename() to fetch week
Posted: Wed Jan 19, 2005 9:05 am
We use the below user function @ work to get the correct weeknumber. Apparently as 1st and 2nd was during week 52, MSSQL fubars, returning false hits (depending on what dates are being used).
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.
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