为什么SQL Server会损失一毫秒?

I have a table structured like this:

我有这样一张桌子:

CREATE TABLE [TESTTABLE]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DateField] [datetime] NULL,
    [StringField] [varchar](50),
    [IntField] [int] NULL,
    [BitField] [bit] NULL
)

I execute the following code:

我执行以下代码:

BEGIN 
   INSERT INTO TESTTABLE (IntField, BitField, StringField, DateField) 
   VALUES ('1', 1, 'hello', {ts '2009-04-03 15:41:27.378'});  

   SELECT SCOPE_IDENTITY()  
END

And then

然后

select * from testtable with (NOLOCK)

and my result shows:

和我的结果显示:

2009-04-03 15:41:27.*377*

for the DateField column.

DateField列。

Any ideas why I seem to be losing a millisecond??

你知道我为什么会失去一毫秒吗?

6 个解决方案

#1

SQL Server only stores time to approximately 1/300th of a second. These always fall on the 0, 3 and 7 milliseconds. E.g. counting up from 0 in the smallest increment:

SQL Server只存储大约1/300秒的时间。它们总是在0、3和7毫秒之间。例如，最小增量从0向上计数:

00:00:00.000
00:00:00.003
00:00:00.007
00:00:00.010
00:00:00.013
...

00:00.00 .00 .00 .00 .00 .00 .00。

If you need that millisecond accuracy, there's no pleasant way around it. The best options I've seen are to store the value in custom number fields and rebuild it every time you fetch the value, or to store it as a string of a known format. You can then (optionally) store an 'approximate' date in the native date type for the sake of speed, but it introduces a conceptual complexity that often isn't wanted.

如果你需要千分之一秒的准确度，就没有什么好办法了。我见过的最佳选项是在自定义数字字段中存储值，并在每次获取值时重新生成它，或者将其存储为已知格式的字符串。然后(可选地)以本机日期类型存储“近似”日期，以提高速度，但它引入了通常不需要的概念复杂性。

#2

SQL Server 2008 has much more precision available. The datetime2 type will accurately store values like this: 2008-12-19 09:31:38.5670514 (accuracy to 100 nanoseconds).

SQL Server 2008有更精确的可用性。datetime2类型将准确地存储如下值:2008-12-19 09:38 .5670514(精度为100纳秒)。

Reference: time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types

参考:time和datetime2——探索SQL Server 2008的新日期/时间数据类型

#3

The SQL Server datetime type only has a 1/300th of a second (~3.33̅ ms) resolution, so you are probably seeing a rounding error.

SQL Server datetime类型只有一个1/300th秒(~ 3.33̅ms)分辨率,所以你可能看到一个舍入误差。

See the MSDN Datetime SQL Server reference

参见MSDN Datetime SQL Server引用

#4

SQL Server is only accurate to 1/300th of a second. It will round values to the nearest 1/300th.

SQL Server的精度只有1/300秒。它将把值四舍五入到最近的1/300。

#5

DATETIME does not have infinite precision - you are probably using a value that cannot accurately be represented with the available bits.

DATETIME没有无限的精度——您可能使用的值不能精确地用可用的位来表示。

#6

I've heard, but can't find an official reference, that SQL Server stores datetime values to a precision of 3 milliseconds.

我听说过，但是找不到一个正式的引用，SQL Server将datetime值存储到3毫秒的精度。

#1