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

Related

• How to get the day of the week in Transact-SQL?
• How to create a date in Transact-SQL?
• How to only get the date part of a DateTime variable in Transact-SQL?
• How to only get the time part of a DateTime variable in Transact-SQL?