38

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.

Vaccano
  • 78,325
  • 149
  • 468
  • 850
  • how are you doing the comparison? – ChrisBint Oct 19 '11 at 15:41
  • 1
    It's a different precision. See the related questions to the side. – Anthony Pegram Oct 19 '11 at 15:41
  • 1
    What version of SQL Server are you on? If 2008 `datetime2` has greater precision. – Martin Smith Oct 19 '11 at 15:43
  • 2
    Just truncating off the ms might not be enough. You could have `09:30:03.999999` rounded to `09:30:04.000000` – Joel Rondeau Oct 19 '11 at 15:44
  • @Joel - I will pull off the milliseconds before I save it in the db the first time. So that the second time it will match. – Vaccano Oct 19 '11 at 16:44
  • @Joel, truncating is more common than rounding when dealing with times - in particular, all the standard DateTime format strings truncate when displaying with less than the maximum (100ns) precision. So 09:30:03.99999 becomes 09:30:03, in the same way that 01/01/2011 23:59 becomes 01/01/2011. – Joe Oct 19 '11 at 17:19
  • @Joe, the way Vaccano states he will do it should be fine. I was worried about what SQL would do with a non-truncated value, but he's taking care of that. – Joel Rondeau Oct 19 '11 at 19:05
  • Anytime I propagate a .net DateTime outside my local program, I assume it's only accurate to a few hundred milliseconds. My reasoning is that UTC time accounts for leap seconds and .net DateTime does not. Typically I will use a smalldatetime in my database and just round my datetime to the nearest minute before sending it off to the db. For datetime midpoint rounding, check out -> http://stackoverflow.com/questions/1393696/rounding-datetime-objects – aj.toulan Dec 09 '13 at 20:41

4 Answers4

48

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
NotMe
  • 87,343
  • 27
  • 171
  • 245
23

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
Otiel
  • 18,404
  • 16
  • 78
  • 126
13

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.

Andacious
  • 1,162
  • 10
  • 17
3

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.

Mark Wilkins
  • 40,729
  • 5
  • 57
  • 110