This afternoon I was writing tests to validate a repository class. I wanted to make sure what went in, was the same as what came out. So I wrote a test to insert a record, then retrieve it and finally compare the two instances. The test itself is not my focus here, but what happened when I compared the returned instance against the original is the real highlight.
Part of the class that is inserted, and retrieved is a DateTime field. My original comparison looked like this…
Assert.AreEqual(payment.DateReceived, retrievedPayment.DateReceived);
The test failed. First thing I thought was – the AreEqual is not comparing the dates and therefore these will bever be equal. So, I changed the test to use the DateTime.Compare() method…
Assert.AreEqual(0, DateTime.Compare(payment.DateReceived, retrievedPayment.DateReceived));
Ok, re-run the tests. Red light. What the? Time to debug and see what’s going on. So stepping through the code I noticed something… The times are different! Not by much, in fact looking at the datetime field in the database, it looked the same. So I checked the ticks. Two milliseconds difference!
Right. I know the issue – so where does it originate. First I checked my code, both in the repository method and the stored proc. Nothing there re-setting the value. Off to Google. Here’s the reason…
From MSDN on the DateTime struct:
“Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar.”
From MSDN on the SQL DateTime type:
“Accuracy – Rounded to increments of .000, .003, or .007 seconds”
So there you have it. There’s a rounding effect applied to times stored in SQL Server 2005. So now we know the issue, what can we do to avoid, or remedy it?
1. Store the DateTime value as the value of the Ticks component using the bigint type in SQL Server
2. If you don’t require the level of precision provided by the .Net DateTime struct, you could potentially ignore the difference, and/or zero the millisecond value.
The best thing about this bug? Occasionally the test would pass!!
— EDIT: Zeroing the millisecond value won’t always work. As pointed out by Tatham over MSN shortly after I originally wrote this values under a second still won’t equate. If you’re going to be comparing sub-second values I would recommend using the Ticks value anyway for a higher level of accuracy.
I was going to write precision but that would have been wrong… 🙂
Steve Godbold 