微小DATEDIFF没有返回预期值 [英] DATEDIFF with minute does not return expected value

问题描述

我说，我有以下SQL Server 2008的表数据：

Say, I have the following SQL Server 2008 table with data:

CREATE TABLE tbl (dtIn DATETIME2, dtOut DATETIME2)
INSERT tbl VALUES
('9/10/2012 5:14:10 AM', '9/10/2012 5:15:09 AM'),
('9/10/2012 5:16:12 AM', '9/10/2012 5:18:12 AM'),
('9/10/2012 5:18:43 AM', '9/10/2012 5:23:04 AM'),
('9/10/2012 5:25:17 AM', '9/10/2012 5:26:05 AM'),
('9/10/2012 5:26:57 AM', '9/10/2012 5:29:19 AM'),
('9/10/2012 5:31:41 AM', '9/10/2012 5:32:41 AM'),
('9/10/2012 5:33:16 AM', '9/10/2012 5:34:08 AM'),
('9/10/2012 5:35:25 AM', '9/10/2012 5:49:46 AM'),
('9/10/2012 5:55:35 AM', '9/10/2012 5:56:48 AM'),
('9/10/2012 5:58:54 AM', '9/10/2012 5:59:59 AM')

然后我跑的这个查询：

WITH ctx AS(
  SELECT datediff(minute, dtIn, dtOut) AS d FROM tbl
  )
SELECT SUM(d) FROM ctx

我拿到30分

但是当我尝试用C＃一样的：

But when I try the same with C#:

double fM = 0;
fM += (DateTime.Parse("9/10/2012 5:15:09 AM") - DateTime.Parse("9/10/2012 5:14:10 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:18:12 AM") - DateTime.Parse("9/10/2012 5:16:12 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:23:04 AM") - DateTime.Parse("9/10/2012 5:18:43 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:26:05 AM") - DateTime.Parse("9/10/2012 5:25:17 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:29:19 AM") - DateTime.Parse("9/10/2012 5:26:57 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:32:41 AM") - DateTime.Parse("9/10/2012 5:31:41 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:34:08 AM") - DateTime.Parse("9/10/2012 5:33:16 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:49:46 AM") - DateTime.Parse("9/10/2012 5:35:25 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:56:48 AM") - DateTime.Parse("9/10/2012 5:55:35 AM")).TotalMinutes;
fM += (DateTime.Parse("9/10/2012 5:59:59 AM") - DateTime.Parse("9/10/2012 5:58:54 AM")).TotalMinutes;

我得到FM = 29.016666666666669。

I get fM = 29.016666666666669.

通过添加Math.Round（）给每个C＃的语句，我得到28.0。
加入Math.Floor（）我得到25.0。
加入Math.Ceiling我得到33.0。

By adding Math.Round() to each C# statement, I get 28.0. By adding Math.Floor() I get 25.0. By adding Math.Ceiling I get 33.0.

有人能解释这种差异？

推荐答案

每个的返回值是测量不同的事情

The return value of each is measuring different things.

这将是谨慎注意的 DATEDIFF 这里文档：

It would be prudent to pay attention to the DATEDIFF docs here:

返回指定的开始日期和结束日期之间越过指定datepart边界的计数（有符号整数）。

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

这会导致以下两种秒间隔：

which leads to the following 2 second interval:

SELECT datediff(minute, '9/10/2012 5:14:59 AM', '9/10/2012 5:15:01 AM') 

返回1，因为它跨越分钟为界。我怀疑你没有采取这种行为考虑在内。

returning 1 because it crosses a minute boundary. I suspect that you did not take this behaviour into account.

这篇关于微小DATEDIFF没有返回预期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！