如何在使用DATEPART时处理DATEFIRST

-- SET DATEFIRST to U.S. English default value of 7.  
SET DATEFIRST 7;
SELECT
  @@DATEFIRST;
SELECT
  GETDATE()
, DATEPART(dw , GETDATE()) AS DayOfWeek;  
-- January 1, 1999 is a Friday. Because the U.S. English default   
-- specifies Sunday as the first day of the week, DATEPART of 1999-1-1  
-- (Friday) yields a value of 6, because Friday is the sixth day of the   
-- week when you start with Sunday as day 1.  

SET DATEFIRST 3;
SELECT
  @@DATEFIRST;
-- Because Wednesday is now considered the first day of the week,  
-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the   
-- week. The following DATEPART function should return a value of 3.  
SELECT
  GETDATE()
, DATEPART(dw , GETDATE()) AS DayOfWeek;
SET DATEFIRST 7;

How do we handle getting the DATEPART (1 = Sunday always) irregardless of DATEFIRST setting?

无论DATEFIRST设置如何，我们如何处理DATEPART（1 =周日总是）？

I really don't want to do a case and subtract...

我真的不想做一个案例并减去......

3 个解决方案

#1

this always seems ridiculous to me, how about

这对我来说总是很荒谬，怎么样

select datediff(day,0, getdate()) % 7

where 6 represents Sunday

其中6代表星期日

or you could do

或者你可以做到

select (datediff(day,0, '2016-07-31') - 5) % 7

to get Sun = 1, Mon = 2, Tue = 3 ... etc

得到Sun = 1，Mon = 2，Tue = 3 ......等等

or you could do this fiddle

或者你可以做这个小提琴

select (datepart(weekday,get_date()) + @@datefirst - 1) % 7 + 1

seems to work for all datepart

似乎适用于所有datepart

    set datefirst  5

select (datepart(weekday,'2016-07-31') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-01') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-02') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-03') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-04') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-05') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-06') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-07') + @@datefirst - 1) % 7 + 1

#2

Perhaps not the most elegant, but instead of doing the subtraction you can just set it to 7 and then back to what ever it was after your DATEPART

也许不是最优雅的，但不是做减法，你可以把它设置为7，然后再回到DATEPART之后的状态

SET DATEFIRST 7;
SELECT DATEPART(dw , GETDATE())             --6

SET DATEFIRST 3;

DECLARE @currentDatefirst int = @@DATEFIRST
SELECT @@DATEFIRST                          --3

SET DATEFIRST 7;
SELECT DATEPART(dw , GETDATE())             --6
SET DATEFIRST  @currentDatefirst
SELECT @@DATEFIRST                          --3

#3

This code selects Monday as the first day of week whatever the setting in your engine:

无论您的引擎中的设置如何，此代码都会选择星期一作为星期的第一天：

((datepart(DW, @YourDateVariable) + @@DATEFIRST + 5) % 7) + 1

by adding @@DATEFIRST value before the modulus operator it neglects the datefirst setting in your SQL engine.

通过在模数运算符之前添加@@ DATEFIRST值，它忽略了SQL引擎中的datefirst设置。

The value 5 is to make Monday as the first day of the week To make Sunday as the first day of the week add 6 to make saturday as the first day of the week then add 0 and so on the rest of the day weeks.

值5是将星期一作为一周的第一天。为了使星期日成为一周的第一天，将星期六作为一周的第一天加上6，然后在当天的其余时间加上0等等。

#1