The day of week for a given date can in Microsoft SQL server be calculated based on the @@datefirst 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 @@datefirstvariable. 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 user-defined 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