DateTime.Compare is not working as expected

Question

I'm inserting customers the following way.

DateTime d = DateTime.Now;

foreach(Customer cus in CustomerList)
{
    cus.EntryDate = d;
}

SaveToDatbase(CustomerList);

Date is getting saved successfully with the correct entry date, but however when I retrieve data back and compare it with variable d, it doesn't match.

foreach(Customer cus in GetFromDatabase())
{
    Response.Write(DateTime.Compare(cus.EntryDate, d));
}

The output is 1 instead of 0. I checked the inserted data and it matches d variable value with milliseconds. Date is stored in sql server database. One thing I realized is if I reset milliseconds to 0 for cus.EntryDate and d, output is 0. What am I doing wrong here?

What database are you storing it in? Some don't have the resolution to store `DateTime` with perfect fidelity. — Jon Hanna, Aug 18 '15 at 22:47
I use Sql Server. I verified that the data getting saved matches d variable. — user3587180, Aug 18 '15 at 22:48

Dai · Accepted Answer · 2015-08-18T22:51:56.483

5

SQL Server's datetime type has a lower resolution than .NET's. As you're using DateTime.Now directly (without rounding it to the nearest second) you'll see differences in the milisecond or tick (100 nanosecond) portions.

According to MSDN, the datetime type in SQL Server has a resolution of approximately 3 miliseconds (specifically: 2015-08-18 15:49:10.000 -> 2015-08-18 15:49:10.003 -> 2015-08-18 15:49:10.007 -> 2015-08-18 15:49:10.010) whereas .NET's DateTime type has a resolution of 100 nanoseconds.

If you want to preserve some kind of equality, I suggest rounding DateTime to the nearest second before inserting into the database:

DateTime now = DateTime.Now;
now = new DateTime(
    now.Ticks - ( now.Ticks % TimeSpan.TicksPerSecond ),
    now.Kind
);

foreach(Customer cus in customerList) cus.EntryDate = now;

edited Aug 18 '15 at 22:51

answered Aug 18 '15 at 22:47

Dai

141,631
28
261
374

So is the best option to reset milliseconds to 0 and compare? I simplified above example, but for the actual app I'm dealing with invoice dates. I don't need precision with milliseconds, but precision with seconds is required because there would be times when two people add two different invoices at the same time. My unique identifies would then be entry date. – user3587180 Aug 18 '15 at 22:51
@user3587180 I updated my response to show how you can zero out sub-second details of a `DateTime` instance. – Dai Aug 18 '15 at 22:54
Side note: I think it would be more readable to create `new DateTime(now.Year, now.Month, now.Days... Seconds)` instead of `%`... Also many people disagree - http://stackoverflow.com/questions/1393696/rounding-datetime-objects. – Alexei Levenkov Aug 18 '15 at 22:54
1

SQLServer's `datetime` is good to an accuracy of 3⅓ms, and .NET's `DateTime` to 100ns, so if you need an accuracy less than around 10ms then rounding to that or less is the easiest way to deal with it. If you do need such accuracy then `datetime2` in SQLServer 2008 and later will serve that need. – Jon Hanna Aug 18 '15 at 23:11

score 1 · Answer 2 · edited May 23 '17 at 12:22

SQL Server's datetime value has a precision of 1 milicsecond, but an accuracy of [approximately] 3.33ms. Further, it doesn't actually "round" the millisecond count. Its "rounding" algorithm is...byzantine, to be kind. But it is, at least, documented. My answer to the question, "Round .NET DateTime milliseconds, so it can fit SQL Server milliseconds", which you can find at https://stackoverflow.com/a/5318635/467473, will

point you to the documentation for how the conversion is performend, and
has an datetime extension method that will convert a CLR System.DateTime value to its corresponding SQL Server datetime equivalent.

One should note that to due to the precision/accuracy difference between the two representations, this is not a round trip conversion.

DateTime.Compare is not working as expected

2 Answers2