Milliseconds value are not getting saved in SQL Server DateTime column

Question

I need to store a DateTime value which is sent to the Database from a C# application which is using DateTime.UtcNow. If I save it to the DateTime column, the milliseconds value are always 000. But while debugging from the application, the milliseconds value is sent from the application to the database. What am I missing?

Answer 1

It might happen because datetime column rounds milliseconds part. From documentation;

Accuracy

Rounded to increments of .000, .003, or .007 seconds

Since you didn't show us how often you store your UtcNow value (I assume you store it as a DateTime, not it's string representation because parameterless ToString and other standard formats does not generates millisecond part usually), this may happen but if you do it in short time intervals, it would be really weird always rounding to 000 as milliseconds part. But of course, we can't know that, yet.

On the other hand, datetime2 type does not do any rounding. And it's accuracy is 100 nanoseconds.

Accuracy

100 nanoseconds

Answer 2

Here's no pleasant way to use Datetime because

SQL Server only stores time to approximately 1/300th of a second. These always fall on the 0, 3 and 7 milliseconds

SQL Server 2008 has much more precision available. The datetime2 datatype will accurately store values like this: 2008-12-19 09:31:38.5670514

See reference documentation