Datefirst in SQL Server Function

Question

Hello, I have a SQL server function where I have to find the weekday number. Now it seems that it depends on the user which result I get. With some users I get a 7 for Sunday, with others a 1. I've inserted Set DATEFIRST into this function, but this entry is deleted when I save the function. Can someone tell me what I have to do to always get the same number for a weekday, regardless of the respective user and his environment? I already have this problem when I run the function in the editor or execute the function using a Select statement Example: SELECT WeekDaty FROM MyFunction ('2024-1-1') Regards

Answer 1

SET commands are not permitted in function.

You can retrieve the current setting of the DATEFIRST option with the function @@datefirst. Here is an example:

CREATE OR ALTER FUNCTION MyFunkyOne(@a date) RETURNS int AS
BEGIN 
   RETURN (datepart(dw, @a) + @@datefirst - 1) % 7 
END 
go
SET DATEFIRST 7
SELECT dbo.MyFunkyOne('2024-02-21')
go
SET DATEFIRST 1
SELECT dbo.MyFunkyOne('2024-02-21')
go
SET DATEFIRST 2
SELECT dbo.MyFunkyOne('2024-02-21')