MSSQL and WEEKDAY

OK. So you can set which day of the week is the "first" and then the DATEPART(weekday,@date) returns a number from 1 to 7 with the specified day being "1". The documentation is rather convoluted, but well ... what would you expect. So everything is nice and dandy isn't it?

No it is not. The catch is that the SET DATEFIRST is not stored with the stored procedure or function, but rather taken from the current connection. So you write for example a function that returns the first Monday at least N days after a specified date and ... pray that someone for whatever reason doesn't set DATEFIRST to anything else than it was set to when you tested the function. Cause the function doesn't remember what it was set to when it was parsed, you can't set it from within the function and there is no way to pass that option to DATEPART() explicitely.

Now that's what I call a stupid interface.


CREATE FUNCTION dbo.GetWeekDay(@Date datetime) RETURNS int
AS
BEGIN
Declare @day int
set @day = 1 + ((@@DATEFIRST + DATEPART( weekday, @Date) - 2) % 7);
return @day
END
go

At last something that's consistent. Agrrrrrr.