blocks|key|1374617|text|SQL+Server的精确度仅为1/300秒。它会将值四舍五入到最接近的1/300。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1374618|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1589859|text|SQL+Server+datetime类型的分辨率仅为1/300秒(~3.33̅ms)，因此您可能会看到舍入误差。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1589860|请参阅MSDN+Datetime+SQL+Server+reference|1589861|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa258277%2528v=sql.80%2529.aspx|1|http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx^0|B|8|B|8|0|0|3|Y|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|H|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

The SQL Server <a href="http://msdn.microsoft.com/en-us/library/aa258277%28v=sql.80%29.aspx" rel="nofollow noreferrer"><code>datetime</code></a> type only has a 1/300th of a second (~3.33̅ ms) resolution, so you are probably seeing a rounding error.

See the <a href="http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx" rel="nofollow noreferrer">MSDN Datetime SQL Server reference</a>

blocks|key|1374690|text|SQL+Server以3毫秒的精度存储日期时间值。(我听说过这个，但找不到官方的参考资料。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1374691|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1374737|text|DATETIME没有无限的精度-您使用的值可能无法用可用位准确表示。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1374738|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1374787|text|SQL+Server仅将时间存储到大约1/300秒。它们总是落在0、3和7毫秒。例如，从0开始，以最小的增量递增：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1374788|00:00:00.000|1374789|00:00:00.003|1374790|00:00:00.007|1374791|00:00:00.010|1374792|00:00:00.013|1374793|..。|1374794|如果你需要毫秒级的精确度，那就没有什么好的办法了。我见过的最好的选择是将值存储在自定义数字字段中，并在每次获取值时重新构建它，或者将其存储为已知格式的字符串。为了提高速度，您可以(可选地)将“近似”日期存储在原生date类型中，但它引入了通常不需要的概念上的复杂性。|1374795|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

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 
...

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.

blocks|key|1590045|text|SQL+Server+2008具有更高的精确度。datetime2类型将准确地存储值，如下所示:+2008-12-19+09:31:38.5670514+(精确度为100纳秒)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1590046|参考：time+and+datetime2+-+Exploring+SQL+Server+2008's+New+Date/Time+Data+Types|offset|length|1590047|entityMap|0|LINK|mutability|MUTABLE|url|http://rwgarrison.com/PASS/TimeDateTime2/^0|0|3|21|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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).

Reference: <a href="http://rwgarrison.com/PASS/TimeDateTime2/" rel="noreferrer">time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types</a>

I have a table structured like this:

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

I execute the following code:

<pre><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
</code></pre>

And then 

<pre><code>select * from testtable with (NOLOCK)
</code></pre>

and my result shows:

<pre><code>2009-04-03 15:41:27.*377*
</code></pre>

for the <code>DateField</code> column.

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

Why is SQL Server losing a millisecond?

SQL Server

我有一个结构如下的表：CREATE TABLE [TESTTABLE](    [ID] [int] IDENTITY(1,1) NOT NULL,    [DateField] [datetime] NULL,    [StringField] [varchar](50),    [IntField] [int] N...

问为什么SQL Server会损失1毫秒？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么SQL Server会损失1毫秒？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么SQL Server会损失1毫秒？
EN