Thursday, October 6, 2011

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
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment