18

I am running SQL Server 2008 on a machine with regional settings that have Monday as the first day of the week. If I create a computed column in a table to compute the day of week for a date field then I get 2 for a Monday date instead of 1.

Is there some property for the table or database or server that I need to set ?

Ben
  • 51,770
  • 36
  • 127
  • 149
Clipper87
  • 211
  • 1
  • 4
  • 7

4 Answers4

34

The first day of the week is based on your language settings of the server. The default setting for us_english is 7 (Sunday)

You can find the current first day of the week by using SELECT @@DATEFIRST

However, you can use DATEFIRST for this. Just put it at the top of your query

SET DATEFIRST 1; this sets Monday to the first day of the week for the current connection.

http://technet.microsoft.com/en-us/library/ms181598.aspx

Sam
  • 7,245
  • 3
  • 25
  • 37
  • I believe you're right it's in the scope of the current connection. I've updated my answer slightly so that there is no confusion. Thanks. – Sam May 20 '14 at 12:54
  • 1
    Thanks Sam. I wonder how this operates with connection pooling... is the connection pool clever enough to segment open connections on this basis, or will the connection be handed back to the connection pool manager and then other pool users "randomly" get a different start day?.... not something I have time to fiddle with, but might be worth looking into if you have time, would possibly add a lot of value to your answer :) – Nathan May 21 '14 at 07:45
13

You can use DATEPART(dw, GETDATE()) but be aware that the result will rely on SQL server setting @@DATEFIRST value which is the first day of week setting (In Europe default value 7 which is Sunday).

Alternative way is to explicitly specify the first day of week value as parameter and avoid depending on @@DATEFIRST setting. You can use the following formula to achieve that when need it:

(DATEPART(dw, GETDATE()) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1

where @WeekStartDay is the first day of the week you want for your system (from 1 to 7 which means from Monday to Sunday).

I have wrapped it into below function so we can reuse it easily:

CREATE FUNCTION [dbo].[GetDayInWeek](@InputDateTime DATETIME, @WeekStartDay INT)
RETURNS INT
AS
BEGIN
    --Note: @WeekStartDay is number from [1 - 7] which is from Monday to Sunday
    RETURN (DATEPART(dw, @InputDateTime) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1 
END

Example usage: GetDayInWeek('2019-02-04 00:00:00', 1)

It is equivalent to following (but independent to DATEFIRST setting):

SET DATEFIRST 1
DATEPART(dw, '2019-02-04 00:00:00')
Minh Nguyen
  • 2,106
  • 1
  • 28
  • 34
8

Just set in query

SET DATEFIRST 1;

Value First day of the week is 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, U.S. English) Sunday

Coder
  • 400
  • 4
  • 19
1

A horrible situation....Say you are using a shared server and apps can get moved around to different servers based on loads and for some reason one server was setup with a different firstDate for another application....and you are returning the week day in a table function (ie can't set firstdate in this function). This will always guarantee Monday as the first day.

CREATE FUNCTION fnGetMondayWD(@WD INT)
RETURNS INT
AS
     BEGIN

         /* think of 1 to 7 as a clock that can rotate forwards or backwards */

         DECLARE @OFFSET INT, @calc INT;
         SET @offset = @@DATEFIRST + @WD - 1;--Monday DateFirst
         SET @calc = IIF(@offset > 7, @offset - 7, @offset); -- could be @offset % 7 (less readable more efficient)
         RETURN @calc;
     END;

go

-- Test Cases


SET datefirst 7
select dbo.fnGetMondayWD(2) Mon,
     dbo.fnGetMondayWD(3)Tue, 
     dbo.fnGetMondayWD(4)Wed, 
     dbo.fnGetMondayWD(5)Thur, 
     dbo.fnGetMondayWD(6)Fri, 
     dbo.fnGetMondayWD(7)Sat, 
     dbo.fnGetMondayWD(1)Sun


SET datefirst 6
select dbo.fnGetMondayWD(3) Mon,
     dbo.fnGetMondayWD(4)Tue, 
     dbo.fnGetMondayWD(5)Wed, 
     dbo.fnGetMondayWD(6)Thur, 
     dbo.fnGetMondayWD(7)Fri, 
     dbo.fnGetMondayWD(1)Sat, 
     dbo.fnGetMondayWD(2)Sun

SET datefirst 5
select dbo.fnGetMondayWD(4) Mon,
     dbo.fnGetMondayWD(5)Tue, 
     dbo.fnGetMondayWD(6)Wed, 
     dbo.fnGetMondayWD(7)Thur, 
     dbo.fnGetMondayWD(1)Fri, 
     dbo.fnGetMondayWD(2)Sat, 
     dbo.fnGetMondayWD(3)Sun

SET datefirst 1
select dbo.fnGetMondayWD(1) Mon,
     dbo.fnGetMondayWD(2)Tue, 
     dbo.fnGetMondayWD(3)Wed, 
     dbo.fnGetMondayWD(4)Thur, 
     dbo.fnGetMondayWD(5)Fri, 
     dbo.fnGetMondayWD(6)Sat, 
     dbo.fnGetMondayWD(7)Sun
Donald Detelj
  • 11
  • 3