MS SQL: First Day of Week

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]
(      
)
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

MS SQL: Day of Week

The day of week for a given date can in Microsoft SQL server be calculated based on the @@FirstDay system variable and the datepart function in Transact-SQL. The value returned from datepart is not constant but depends on the first day of week specified by the @@FirstDay variable. In modern programming languages like C# we will get a constant value for each day of the week. In .Net the DayOfWeek function will return 0 for sundays, 1 for mondays, etc.

A constant DayOfWeek value can be calculated in the following way:
(((@@datefirst-1) + datepart(weekday, @dtDate)) % 7)

The full Transact-SQL userdefined function is as follows:

-- =============================================

-- Description: Returns the weekday number of a given date

--

--    0 - Sunday

--    1 - Monday

--    2 - Tuesday

--    3 - Wednesday

--    4 - Thursday

--    5 - Friday

--    6 - Saturday

--                             

-- The DayOfWeek is calculated based on the current

-- @@DateFirst settings

-- between the current date and the beginning of the week

-- =============================================

CREATE FUNCTION [dbo].[sudf_Calendar_DayOfWeek]

(      

        @dtDate DateTime        -- Current date

)

RETURNS int

AS

BEGIN

        -- Variables

        declare @intDayOfWeek   int;

        -- Get the day of week

        set @intDayOfWeek = (((@@datefirst-1) + datepart(weekday, @dtDate)) % 7);

        -- Calculate the offset

        return @intDayOfWeek;

END

Source: Coragi.com