Milliseconds in my DateTime changes when stored in SQL Server

Question

I have a date time that I generate like this:

DateTime myDateTime = DateTime.Now; 

I then store it in the database (in a DateTime typed column) with Entity Framework. I then retrieve it with OData (WCF Data Services).

When it goes in the TimeOfDay value is: 09:30:03.0196095

When it comes out the TimeOfDay value is: 09:30:03.0200000

The net effect of this makes it so that the Milliseconds are seen as 19 before it is saved and 20 after it is re-loaded.

So when I do a compare later in my code, it fails where it should be equal.

Does SQL Server not have as much precision as .NET? Or is it Entity Framework or OData that is messing this up?

I will just truncate off the milliseconds (I don't really need them). But I would like to know why this is happening.

Answer 1

This really depends on the version of SQL server you are using.

The resolution of the date time field is to 3 decimal places: For example: 2011-06-06 23:59:59.997 and is only accuracte to within 3.33 ms.

In your case, 09:30:03.0196095 is being rounded up to 09:30:03.020 on storage.

Beginning with SQL 2008, other data types were added to provide more detail, such as datetime2 which has up to 7 decimal places and is accurate to within 100ns.

See the following for more information:

http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

I think your best bet is to provide the rounding to the second PRIOR to storing it in SQL server if the milliseconds is unimportant.