In Transact-SQL the first day of week can be calculated based on the @@FirstDay system variable.
The variable will return different values depending of configured language of the server. For US English the first day of week will be set to 7, i.e. Sunday. In .NET a different day range is used.
We can use the following calculation to ensure that Sunday is 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]
declare @intFirstDayOfWeek int;
-- The first day of week
set @intFirstDayOfWeek = (@@datefirst % 7);
-- Return the first day of the week