为什么DATEDIFF wk每周显示6天?

I was usingDATEADD(ww, DATEDIFF(ww, 0, GETDATE()), 0) to get the start of the week to run a couple of reports, however when it was run on a Sunday it was using the coming week rather than the current week. when I run the following I was perplexed by the results as it shows even though the Day Diff is 6 the week diff is 1 not Zero. Why is this the case?

我使用dateadd (ww, DATEDIFF(ww, 0, GETDATE()， 0))来获取本周的开始运行几个报告，但是当它在周日运行时，它使用的是下一周而不是当前一周。当我运行下面的程序时，我被结果迷惑了，因为它显示了即使Diff是6周Diff是1不是0。为什么会这样?

SELECT DATEDIFF(WEEK, '20160527', GETDATE()) Wks, DATEDIFF(Day, '20160527', GETDATE()) Dys
SELECT DATEDIFF(WEEK, '20160528', GETDATE()) Wks, DATEDIFF(DAY, '20160528', GETDATE()) Dys
SELECT DATEDIFF(WEEK, '20160529', GETDATE()) Wks,DATEDIFF(Day, '20160529', GETDATE()) Dys
SELECT DATEDIFF(WEEK, '20160530', GETDATE()) Wks,DATEDIFF(DAY, '20160530', GETDATE()) Dys

Results

结果

1 个解决方案

#1

DATEDIFF shows the number of boundaries crossed, not an actual difference in time that gets rounded, so that can cause some "odd" behaviors like this depending on when the days fall. As Damien_The_Unbeliever points out, for weeks the start of the week is always considered to be Sunday for DATEDIFF.

DATEDIFF显示了跨越的边界的数量，而不是得到整数的实际时间差异，因此这可能导致一些“奇怪”的行为，如这取决于天何时落。正如damien_the_unfaithful指出的那样，每周的开始都被视为DATEDIFF的周日。

For example, from Saturday to Sunday will still show a DATEDIFF of 1 for weeks even though it's also just one day.

例如，从周六到周日，即使只是一天，也会显示出1个星期的数据。

Similarly, from 12:01 AM to 11:59 PM is a DATEDIFF of 0 for days, but 11:59 PM to 12:01 AM is a DATEDIFF of 1 for days.

同样，从12点01分到11点59分的日期编辑数天为0，而从11点59分到12点01分的日期编辑数天为1。

#1