Wrong week numbers

  • Please can someone tell me why this resul;ts from the below code produce an incorrect week number?!?!?!

     

    ----------------------------------------------------------------

    set nocount on

    --set DATEFIRST 1

    DECLARE @counter int

    set @counter = 0

    declare @looper datetime

    set @looper = '20041225'

    WHILE @counter < 15

    BEGIN

    PRINT CAST(@looper as varchar(11)) + ' - week ' + CAST(DATEPART(wk,@looper) as varchar)

    set @looper = DATEADD(d,1,@looper)

    set @counter = @counter + 1

    END

    -----------------------------------------------------------------------

  • This was removed by the editor as SPAM

  • The DATEPART(WK,...) function does not return the ISO week number. If you need the standard week number, use the following function (the first example from the BOL topic "CREATE FUNCTION"):

    CREATE FUNCTION ISOweek  (@DATE datetime)
    RETURNS int
    AS
    BEGIN
       DECLARE @ISOweek int
       SET @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 (@ISOweek=0) 
          SET @ISOweek=dbo.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 @ISOweek=1
       RETURN(@ISOweek)
    END

    Make sure that you execute the following:

    SET DATEFIRST 1

    before calling the ISOWeek function.

    Razvan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply