January 4, 2005 at 5:35 am
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
-----------------------------------------------------------------------
January 7, 2005 at 8:00 am
This was removed by the editor as SPAM
January 8, 2005 at 11:53 am
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