In Transact-SQL the first day of week can be calculated based on the @@datefirst system variable. The variable will return different values depending on which language has been configured for the server. For US English the first day of week will be set to 7, i.e. Sunday, while for other regions the first day will be Monday. In .NET a different day range is used, where the Sunday is always has index 0.
We can use the following calculation to ensure that Sunday is always 0, Monday = 1, etc.
@@datefirst % 7
The full user-defined function is as follows:
-- Description: Returns the first weekday of the week -- based on the system settings on the DB server: -- 0 - Sunday -- 1 - Monday -- 2 - Tuesday -- 3 - Wednesday -- 4 - Thursday -- 5 - Friday -- 6 - Saturday -- -- ============================================= ALTER FUNCTION [dbo].[sudf_Calendar_FirstDayOfWeek] ( ) RETURNS int AS BEGIN -- Variables declare @intFirstDayOfWeek int; -- The first day of week set @intFirstDayOfWeek = (@@datefirst % 7); -- Return the first day of the week return @intFirstDayOfWeek; END