SQL DATEPART(dw,date) 需要 monday = 1 和 sunday = 7 [英] SQL DATEPART(dw,date) need monday = 1 and sunday = 7

问题描述

我有一个查询，可以获取日期的 WeekDay，但默认情况下:

I have a Query where I get the WeekDay of a date but by default:

• 星期日 = 1

• Sunday = 1

今天 = 2

等

函数是:

DATEPART(dw,ads.date) as weekday

我需要这样的结果:

• 星期日 = 7

• Sunday = 7

星期一 = 1

等

有什么捷径可以做到这一点吗?或者我必须做一个CASE 语句?

Is there any shortcut to do this? Or I will have to do a CASE statement?

推荐答案

您可以使用如下公式:

(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.

添加:为了不受 DATEFIRST 变量的影响(它可以设置为 1 到 7 之间的任何值)，真正的公式是:

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 :

(weekday  + @@DATEFIRST + 5) % 7 + 1

这篇关于SQL DATEPART(dw,date) 需要 monday = 1 和 sunday = 7的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！