Appearently you can issue SET DATEFIRST from within a UDF, although I can't find that explicitly stated in the BOL anywhere. Have you checked MSDN?
-Mike
>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
