问题描述
我说,我有以下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.
这将是谨慎注意的这里文档:
It would be prudent to pay attention to the DATEDIFF docs here:
返回指定的开始日期和结束日期之间越过指定datepart边界的计数(有符号整数)。
这会导致以下两种秒间隔:
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没有返回预期值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!