为什么 SQL Server 会丢失一毫秒? [英] Why is SQL Server losing a millisecond?

问题描述

我有一个这样结构的表格:

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
)

我执行以下代码:

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

   SELECT SCOPE_IDENTITY()  
END

然后

select * from testtable with (NOLOCK)

我的结果显示:

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

对于 DateField 列.

任何想法为什么我似乎失去了一毫秒??

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

推荐答案

SQL Server 仅存储大约 1/300 秒的时间.这些总是落在 0、3 和 7 毫秒.例如.以最小的增量从 0 开始计数:

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:

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.

这篇关于为什么 SQL Server 会丢失一毫秒?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！