CODEWITHSUNDEEP
c#.netsql-server
Vaccano
Vaccano

Reputation: 82291

Milliseconds in my DateTime changes when stored in SQL Server

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.

Upvotes: 38

Views: 24123

Answers (4)

ChrisLively
ChrisLively

Reputation: 88044

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.

Upvotes: 48

Andacious
Andacious

Reputation: 1172

For those who do not have the ability to use DateTime2 in SQL (ex: like me using tables that are generated by a separate system that would be expensive to change for this single issue), there is a simple code modification that will do the rounding for you.

Reference System.Data and import the System.Data.SqlTypes namespace. You can then use the SqlDateTime structure to do the conversion for you:

DateTime someDate = new SqlDateTime(DateTime.Now).Value;

This will convert the value into SQL ticks, and then back into .NET ticks, including the loss of precision. :)

A word of warning, this will lose the Kind of the original DateTime structure (i.e. Utc, Local). This conversion is also not simply rounding, there is a complete conversion including tick calculations, MaxTime changes, etc.. So don't use this if you are relying on specific indicators in DateTime as they could be lost.

Upvotes: 13

Otiel
Otiel

Reputation: 18743

This is due to the precision of the SQL datetime type. According to msdn:

Datetime values are rounded to increments of .000, .003, or .007 seconds

Look at the Rounding of datetime Fractional Second Precision section of this msdn page and you'll understand how the rounding is done.

As indicated by others, you can use datetime2 instead of datetime to have a better precision:

  • datetime time range is 00:00:00 through 23:59:59.997
  • datetime2 time range is 00:00:00 through 23:59:59.9999999

Upvotes: 23

Mark Wilkins
Mark Wilkins

Reputation: 41222

The precision of DateTime in SQL Server is milliseconds (.fff). So 0.0196 would round to 0.020. If you can use datetime2, you get a higher precision.

Upvotes: 3

Related Questions