Thanks for the reply. I was unaware of the determinism issue and I will read
about it. It this particular case the fn performs some calcuations based on
the day of the week of the input date and the SET was to insure that the
first day was set to Sunday( the default). In other cases, I might need to
set it to Monday.
Since I posted this question, I found a reference to exactly this issue in
the help under CREATE FUNCTION.
--
JB
"Russell Fields" wrote:
> JB,
>
> The issue is determinism. User-defined functions must be deteministic and
> the Books Online have a discussion of determinism. If you describe what you
> hope to do by setting DATEFIRST, there may be another way of getting your
> answer. (And, of course, UDFs cannot modify any stored data.)
>
> RLF
> "JB" <JB@discussions.microsoft.com> wrote in message
> news:BC74DACA-B0CA-42BD-9036-8D3847421380@microsoft.com...
> >I am trying to use SET DATEFIRST in a user defined function and get error
> > 443, Invalid use of 'SET COMMAND' within a function
> >
> > An example ..
> >
> > CREATE FUNCTION dbo.fnGet( @dtDate smalldatetime )
> > RETURNS smallint AS
> >
> > BEGIN
> > SET DATEFIRST 7 -- raises error 443
> >
> > ....
> >
> > RETURN .....
> >
> > END
> >
> > Is there some syntax that will work insida a function?
> > --
> > JB
>
>
>