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

Microsoft SQL Server1 Comment on MS SQL: Get the First Day of Week in Transact-SQL

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

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

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

One thought on “MS SQL: Get the First Day of Week in Transact-SQL

Leave a Reply

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

Back To Top


Subscribe to our newsletter