SQL DATEPART(dw,date) 需要 monday = 1 和 sunday = 7
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24877124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DATEPART(dw,date) need monday = 1 and sunday = 7
提问by VAAA
I have a Query where I get the WeekDayof a date but by default:
我有一个查询，可以获取某个日期的WeekDay，但默认情况下：
Sunday = 1
Moday = 2
etc.
星期日 = 1
今天 = 2
等等。
The function is:
功能是：
DATEPART(dw,ads.date) as weekday
I need the result so:
我需要这样的结果：
Sunday = 7
Monday = 1
etc.
星期日 = 7
星期一 = 1
等等。
Is there any shortcut to do this? Or I will have to do a CASE statement?
有没有捷径可以做到这一点？或者我将不得不做一个CASE statement？
采纳答案by Greg Hewgill
You can use a formula like:
您可以使用如下公式：
(weekday + 5) % 7 + 1
If you decide to use this, it would be worth running through some examples to convince yourself that it actually does what you want.
如果您决定使用它，那么通过一些示例来说服自己它确实可以满足您的需求是值得的。
addition: for not to be affected by the DATEFIRST variable (it could be set to any value between 1 and 7) the real formula is :
另外：为了不受 DATEFIRST 变量的影响（它可以设置为 1 到 7 之间的任何值），真正的公式是：
(weekday + @@DATEFIRST + 5) % 7 + 1
回答by SQLChao
This will do it.
这将做到。
SET DATEFIRST 1;
-- YOUR QUERY
Examples
例子
-- Sunday is first day of week
set datefirst 7;
select DATEPART(dw,getdate()) as weekday
-- Monday is first day of week
set datefirst 1;
select DATEPART(dw,getdate()) as weekday
回答by DavidG
回答by Gordon Linoff
I would suggest that you just write the case statement yourself using datename():
我建议您使用datename()以下方法自己编写 case 语句：
select (case datename(dw, aws.date)
when 'Monday' then 1
when 'Tuesday' then 2
when 'Wednesday' then 3
when 'Thursday' then 4
when 'Friday' then 5
when 'Saturday' then 6
when 'Sunday' then 7
end)
At least, this won't change if someone changes the parameter on the day of the week when weeks begin. On the other hand, it is susceptible to the language chosen for SQL Server.
至少，如果有人在星期开始的那一天更改参数，这不会改变。另一方面，它容易受到为 SQL Server 选择的语言的影响。
回答by Ali
You can use this formula regardless of DATEFIRSTsetting :
无论DATEFIRST设置如何，您都可以使用此公式：
((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - [first day that you need] ) % 7) + 1;
for monday = 1
为了 monday = 1
((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 1 ) % 7) + 1;
and for sunday = 1
并为 sunday = 1
((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 7 ) % 7) + 1;
and for friday = 1
并为 friday = 1
((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 5 ) % 7) + 1;
回答by SharkAl
This is caused by the account the SQL Server service is run under. For example;
这是由运行 SQL Server 服务的帐户引起的。例如;
If the SQL Server Service is run under DOMAIN\MyUserAccount then this will need to be a login and set with the relevant Language.
如果 SQL Server 服务在 DOMAIN\MyUserAccount 下运行，则这将需要登录并使用相关语言进行设置。
If this account isn't set then SQL Server will default to the sa account and the Language that runs under.
如果未设置此帐户，则 SQL Server 将默认使用 sa 帐户和在其下运行的语言。
I found that our sa account was set to English which had Monday as DW = 2. The DOMAIN\MyUserAccount Account was setup and changed to British English and DW for Monday was being returned as 1.
我发现我们的 sa 帐户设置为英语，星期一为 DW = 2。DOMAIN\MyUserAccount 帐户已设置并更改为英式英语，星期一的 DW 返回为 1。
Hope this helps
希望这可以帮助
回答by pkargr
Another solution is the following:
另一种解决方案如下：
ISNULL(NULLIF(DATEPART(dw,DateField)-1,0),7)
回答by Benjas
Try this:
尝试这个：
CREATE FUNCTION dbo.FnDAYSADDNOWK(
@addDate AS DATE,
@numDays AS INT
) RETURNS DATETIME AS
BEGIN
WHILE @numDays > 0 BEGIN
SET @addDate = DATEADD(day, 1, @addDate)
IF DATENAME(DW, @addDate) <> 'sunday' BEGIN
SET @numDays = @numDays - 1
END
END
RETURN CAST(@addDate AS DATETIME)
END
回答by Alivina
Looks like the DATEFIRST settings is the only way, but it's not possible to make a SET statement in a scalar/table valued function. Therefore, it becomes very error-prone to the colleagues following your code. (become a trap to the others)
看起来 DATEFIRST 设置是唯一的方法，但不可能在标量/表值函数中创建 SET 语句。因此，对于遵循您的代码的同事来说，它变得非常容易出错。（成为别人的陷阱）
In fact, SQL server datepart function should be improved to accept this as parameter instead.
事实上，应该改进 SQL 服务器的 datepart 函数以接受它作为参数。
At the meantime, it looks like using the English Name of the week is the safest choice.
同时，使用星期的英文名称似乎是最安全的选择。
回答by Benjamin
I think this could work:
我认为这可以工作：
select
case when datepart(dw,[Date]) = 1 then 7 else DATEPART(DW,[Date])-1 end as WeekDay
