Server: Msg 443, Level 16, State 2, Procedure ISOweek, Line 8
Invalid use of 'SET COMMAND' within a function.

Error 443 is explained in TSQL help as
'Invalid use of '%s' within a function. ' which is great to know, but how do I get around that?

ALTER   FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS

BEGIN
   SET DATEFIRST 1
   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


This function is called from a view and the whole thing works very well,
except for setting the DATEFIRST option.

But without setting DATEFIRST the function uses whatever the settings are on the server.

TIA

Peter Pirker


Whosoever shall not fall by the sword or by famine, shall fall by pestilence, so why bother shaving?

(Woody Allen)