MS SQL: Get the Day of Week in Transact-SQL

Microsoft SQL ServerLeave a Comment on MS SQL: Get the Day of Week in Transact-SQL

MS SQL: Get the Day of Week in Transact-SQL

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
        -- Variables
        declare @intDayOfWeek   int;

        -- Get the day of week
        set @intDayOfWeek = (((@@datefirst-1) + datepart(weekday, @dtDate)) % 7);

        -- Calculate the offset
        return @intDayOfWeek;


Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top