Converting from DateTime to SqlDateTime is inaccurate

Question

I have a method that gets a date property from a source (either a DataRow or a 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 appears to be changing the date slightly so that my test always fails.

Does anyone know why this method would be converting wrongly?

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 - the SQL Server DATETIME has an accuracy of 3.33ms - the .NET datatype however can represent single milliseconds.

Therefore, you will have some rounding issues at times.

Read a lot more about the DATETIME datatype and its properties and quirks here:

Demystifying the SQL Server DATETIME datatype

Also, SQL Server 2008 introduced a slew of new date-related data types - read more about those here:

SQL Server 2008 New DATETIME datatypes

These types do include a DATETIME2 datatype which is accurate to 100ns.

Answer 2

This is documented precision of TSQL date-time - see section titled "Rounding of datetime Fractional Second Precision" at this link from MSDN. 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.

Further, MS recommends to use newer data types (such as datetime2, date, time etc) instead of date-time. The data-type datetime2 introduced with Sql Server 2008 has similar precision (100ns) (same as .NET date/time structure) and it also supports ISO 8601 string format that would make conversion from .NET date/time to SQL date-time simpler ((i.e. when you are forced to use literals instead of parametrized query) and loss free.