为什么SQL Server dateadd datediff在使用周时表现得像这样

When using the dateadd/datdiff trick to round dates I've always had very predictable and understandable results with days and months. I am now using it for the first time with weeks and am experiencing something I was not expecting.
The 19th and 26th of July 2015 seem to get moved into what I consider the next week. The following example illustrates the actual behavior in the second column and the expected behavior in the third column. I'm trying to understand WHY and HOW there is a difference.

当使用dateadd / datdiff技巧来整理日期时，我总是有几天和几个月可以预测和可理解的结果。我现在第一次使用它几周，我正在经历一些我没想到的事情。 2015年7月19日和26日似乎进入了我认为的下周。以下示例说明了第二列中的实际行为以及第三列中的预期行为。我想了解为什么以及如何有所不同。

declare @n as table ([N] [int] )

insert into @n ( [N] ) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
select n
,      dateadd(dd,n,'2015-jul-17')
,      dateadd(wk,datediff(wk,0,  dateadd(dd,n,'2015-jul-17')  ),0)
,      dateadd(dd,datediff(dd,0,  dateadd(dd,n,'2015-jul-17')  )/7*7,0)
from @n
order by n

2 个解决方案

#1

The problem is DATEDIFF() by week using Sunday as the start day of the week instead of Monday.

问题是DATEDIFF（）按星期使用星期日作为一周的开始日而不是星期一。

A trick is -1:

一招是-1：

declare @n as table ([N] [int])
declare @start datetime = '1900-01-01'


insert into @n ( [N] ) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
select n
,                           dateadd(dd,n,@start) as [Date]
,dateadd(wk,datediff(wk,0,  dateadd(dd,n,@start) -1 ),0) as [WeekStart (by WeekDiff)]
,dateadd(dd,datediff(dd,0,  dateadd(dd,n,@start)    )/7*7,0) as [WeekStart (by DayDiff)]
,           datediff(wk,0,  dateadd(dd,n,@start)) [WeekDiff]
from @n
order by n

Is it possible to set start of week for T-SQL DATEDIFF function?

是否可以为T-SQL DATEDIFF函数设置星期开始？

Edit

编辑

Using DATEPART(WEEKDAY) with SET DATEFIRST 1

使用DATEPART（WEEKDAY）和SET DATEFIRST 1

SET DATEFIRST 1

;with dates(value) as (select convert(date, dateadd(dd,n,@start)) from @n)
select *, DATEADD(Day, -DATEPART(WEEKDAY, value) + 1, value) from dates

#2

This date rounding algorithm, translated to English, could be worded as follows:

这个日期舍入算法，翻译成英语，可以措辞如下：

Add a number of [Time Unit] to the start of calendar (Day 0)

将一些[时间单位]添加到日历的开头（第0天）

where [Time Unit] is Year/Month/Days/Hours etc..... And Start of calendar is 1900/01/01 00:00:00 in T-SQL.

其中[时间单位]是年/月/日/小时等......日历的开始时间是1900/01/01 00:00:00在T-SQL中。

1900/01/01 is a Monday.

1900/01/01是星期一。

So you are adding the number of full weeks to a Monday. And hence the week is always rounded to Monday. This throws 7/19 and 7/26 (Sundays) to the end of the week.

所以你要在周一增加整周的数量。因此，本周总是四舍五入到周一。这将引发7/19和7/26（星期日）到本周末。

#1