为什么 SQL Server 会丢失一毫秒？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/715432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is SQL Server losing a millisecond?
提问by sproketboy
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 DateFieldcolumn.
为DateField列。
Any ideas why I seem to be losing a millisecond??
任何想法为什么我似乎失去了一毫秒？
回答by Whatsit
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.000
00:00:00.003
00:00:00.007
00:00:00.010
00:00:00.013
...
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.
如果您需要毫秒级的准确度，没有什么令人愉快的方法可以绕过它。我见过的最佳选择是将值存储在自定义数字字段中并在每次获取值时重建它，或者将其存储为已知格式的字符串。然后，为了速度，您可以（可选）在本机日期类型中存储“近似”日期，但它引入了通常不需要的概念复杂性。
回答by Rob Garrison
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:31:38.5670514（精确到 100 纳秒）。
Reference: time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types
回答by Peter M
The SQL Server datetimetype only has a 1/300th of a second (~3.33? ms) resolution, so you are probably seeing a rounding error.
SQL Serverdatetime类型只有 1/300 秒 (~3.33? ms) 的分辨率，因此您可能会看到舍入错误。
回答by Tom H
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。
回答by Tom H
DATETIME does not have infinite precision - you are probably using a value that cannot accurately be represented with the available bits.
DATETIME 没有无限精度 - 您可能正在使用无法用可用位准确表示的值。
回答by bdukes
SQL Server stores datetime values to a precision of 3 milliseconds. (I've heard about this, but can't find an official reference.)
SQL Server 以 3 毫秒的精度存储日期时间值。（我听说过这个，但找不到官方参考。）
