Conversion from DateTime to SqlDateTime is inaccurate

Question

I have a method that gets the date property from the source (either DataRow or SqlDataReader ):

 protected SqlDateTime GetSqlDateTimePropertyValue(string propertyName, object source) { var objValue = GetPropertyValue(propertyName, source); var value = objValue == DBNull.Value ? null : (DateTime?)objValue; var sqlValue = new SqlDateTime(); if (value.HasValue) sqlValue = value.Value; return sqlValue; } 

but the conversion seems to change the date a bit, so my test always fails.

Does anyone know why this method will not be converted correctly?

At the end of the method, it looks like the conversion to SqlDateTime does some rounding:

 value.Value.Ticks: 634698391707468296 sqlValue.Value.Ticks: 634698391707470000 

Answer 1

Yes. SQL Server DATETIME has an accuracy of 3.33 ms. However, the .NET data type can be single milliseconds.

Therefore, from time to time you will have problems with rounding.

Read more about the DATETIME type and its properties and quirks here:

DATETIME SQL Server Data Type Demystification

In addition, SQL Server 2008 introduced many new date-related data types: read more here:

New DATETIME SQL Server 2008 Data Types

These types include the DATETIME2 data type, which is accurate to 100 ns.

Answer 2

This is a documented TSQL date date precision - see the "Rounding up a fractional second precision datetime precision" section of this MSDN link . Quote from the section:

datetime values ​​are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

.NET date time has better precision.

In addition, MS recommends using newer data types (for example, datetime2, date, time, etc.) instead of date-time. The datetime2 datatype introduced with Sql Server 2008 has similar accuracy (100 ns) (the same as the .NET date and time structure), and also supports ISO 8601, which will do the conversion from .NET date / time to SQL date-time is simpler ((i.e. when you are forced to use literals instead of a parameterized query) and losses are free.

Answer 3

The SqlDateTime object has less precision than the .NET DateTime object, so it will lose some accuracy when converting to SQL.