How to set Monday as the first day of the week in SQL Server

Question

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 calculated column in the table to calculate the day of the week for the date field, then I get 2 for the Monday date instead of 1.

Is there any property for a table or database or server that I need to set?

Answer 1

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

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

---

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

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

Answer 2

Just set the request

SET DATEFIRST 1;

Value First day of the week 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, United States) Sunday

Answer 3

You can use DATEPART(dw, GETDATE()) but remember that the result will depend on the @@DATEFIRST parameter of the SQL server, which is the value of the first day of the week (in Europe, the default value is 7, which is Sunday).

An alternative way is to explicitly specify the value of the first day of the week as a parameter and avoid dependence on @@DATEFIRST . You can use the following formula to achieve this when necessary:

 (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 Monday through Sunday).

I wrapped it in a function below so that we can easily use it:

 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)

This is equivalent to the following (but independent of the DATEFIRST setting):

 SET DATEFIRST 1 DATEPART(dw, '2019-02-04 00:00:00') 

Answer 4

A terrible situation .... Suppose you use a shared server, and applications can move to other servers depending on the load, and for some reason one server was configured with another firstDate for another application .... and you return the day of the week in a table function (that is, it cannot set the first date in this function). This always guarantees 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