MSSQL issue using datename() to fetch week

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

MSSQL issue using datename() to fetch week

Post by JAM »

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. :wink:

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
Post Reply