Why Does SQL Server Lose Milliseconds When Inserting Datetime Values?

SQL Server date and time precision issues

When inserting values ​​into datetime columns in SQL Server, you may experience unexpected millisecond loss. For example, the following query:

<code class="language-sql">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>

Subsequently using the SELECT * FROM TESTTABLE WITH (NOLOCK) query, the value of the DateField column may appear as:

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

Explanation of difference in millisecond accuracy

This apparent loss of milliseconds is due to storage limitations of SQL Server. SQL Server's time precision is approximately 1/300th of a second, and these intervals always fall on 0, 3, and 7 milliseconds. Therefore, in the example above, the value is rounded down to the nearest 3 millisecond interval, resulting in the observed .377 suffix.

Meeting the Millisecond Precision Challenge

If millisecond accuracy is critical, there is no easy fix. Suggested solutions include:

• Custom numeric field: Store the timestamp as a custom numeric field and reconstruct it on retrieval.
• String Storage: Store the timestamp as a string in a known format.

While these methods add additional complexity, they can provide the necessary precision when millisecond precision is absolutely required.

The above is the detailed content of Why Does SQL Server Lose Milliseconds When Inserting Datetime Values?. For more information, please follow other related articles on the PHP Chinese website!