我正在使用以下SQL获取最新的星期一:
SELECT CAST(DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS DATE)
DATEDIFF(wk,0,GETDATE())
给出6374
默认情况下,SQL server周从周日开始。那么,在0的基础上加6374周(或任意周数(是如何得出周一的呢?
我尝试将DATEFIRST设置为1,以表示星期一开始。然而,如果今天是周日,那么它会给出下一个周一——而我想要上一个周一:
DECLARE @DT DATETIME
SET @DT='20220306'
SELECT CAST(DATEADD(wk, DATEDIFF(wk,0,@DT), 0) AS DATE)
要计算非星期一工作日的前一个星期一,可以使用此计算
SELECT DATEADD(day, -(5+@@DATEFIRST+DATEPART(weekday,@DT))%7, @DT)
@@DATEFIRST
变量用于使计算独立于DATEFIRST设置。
例如:
SET DATEFIRST 1 SELECT DT , DATENAME(weekday, DT) as WeekdayName , DATEADD(day, -(5+@@DATEFIRST+DATEPART(weekday,[DT]))%7, [DT]) AS Monday , -(5+@@DATEFIRST+DATEPART(weekday,[DT]))%7 AS DowDiff , CAST(DATEADD(week, DATEDIFF(week,0,[DT]), 0) AS DATE) AS Df7Monday , DATEPART(week, [DT]) AS Week , DATEDIFF(week,0,[DT]) AS WeeksDiff FROM test;
DT|WeekdayName|Monday|DowDiff|Df7Monday|Week|WeeksDiff:--------|:----------|:--------|-------:|:---------|----:|--------:1900-01-01|星期一|1900-01-01|0|11900-01-01|1|02022-02-27 |周日|2022-02-21|-6|2022-02-20|9|63742022-02-28 |星期一|2022-02-28|0|2022-02-20|10|63742022-03-01|2022-02-28|-1|2022-02-208|10|63742022-03-02|周三|2022-02-28|-2|2022-02-20|10|63742022-03-03|2022-02-28|-3|2022-02-208|10|63742022-03-04|2022-02-28|-4|2022-02-208|10|63742022-03-05|星期六|2022-02-28|-5|2022-02-20|10|63742022-03-06|2022-02-28|-6|2022-03-07|10|63752022-03-07 |星期一|2022-03-07|0|2022-02-03-07|11|6375
SET DATEFIRST 7 SELECT DT , DATENAME(weekday, DT) as WeekdayName , DATEADD(day, -(5+@@DATEFIRST+DATEPART(weekday,[DT]))%7, [DT]) AS Monday , -(5+@@DATEFIRST+DATEPART(weekday,[DT]))%7 AS DowDiff , CAST(DATEADD(week, DATEDIFF(week,0,[DT]), 0) AS DATE) AS Df7Monday , DATEPART(week, [DT]) AS Week , DATEDIFF(week,0,[DT]) AS WeeksDiff FROM test;
DT|WeekdayName|Monday|DowDiff|Df7Monday|Week|WeeksDiff:--------|:----------|:--------|-------:|:---------|----:|--------:1900-01-01|星期一|1900-01-01|0|11900-01-01|1|02022-02-27 |周日|2022-02-21|-6|2022-02-20|10|63742022-02-28 |星期一|2022-02-28|0|2022-02-20|10|63742022-03-01|2022-02-28|-1|2022-02-208|10|63742022-03-02|周三|2022-02-28|-2|2022-02-20|10|63742022-03-03|2022-02-28|-3|2022-02-208|10|63742022-03-04|2022-02-28|-4|2022-02-208|10|63742022-03-05|星期六|2022-02-28|-5|2022-02-20|10|63742022-03-06|2022-02-28|-6|2022-03-07|11|63752022-03-07 |星期一|2022-03-07|0|2022-02-03-07|11|6375
db<gt;小提琴这里
计算DATEDIFF(wk,'1900-01-01',[DT])
不受DATEFIRST设置的影响
您可以在2个测试结果中看到
只需比较周&日期2022-03-07
的WeeksDiff。
正如@JeroenMoster所指出的,在这里记录了这一点
指定SET DATEFIRST对DATEDIFF没有影响。DATEDIFF始终使用周日作为一周中的第一天以确保功能以确定性的方式操作。