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
