Houston, we have a problem.

A few years ago I added a little function to my utilities library. I was addressing a problem that I did not even know exists. Here is the pseudocode to a test I wrote that was failing, simplified down to the applicable lines of code:

  1. Create an object and set all fields, including a date field.
  2. Save the object to a database table, again, the database table has a SQL datetime field corresponding to the C# DateTime property.
  3. Instantiate a new object.
  4. Load from the database into the new object.
  5. Compare the objects, including the DateTime field.
  6. TEST FAIL.

This was a problem that I did not notice until I wrote the unit tests for the project, because I was not using the property to compare exact datetimes, so I did not require this accuracy.

So, herein lies the problem.

SQL Datetime and C# DateTime are different precisions! How different? SQL datetime is precise to about the millisecond, and C# is precise to 100 nanoseconds. So, the precision is 10,000 times more in C# than in SQL Server. But hey, I develop business solutions, not missile guidance systems (that you know of…), so I just need to see if these dates are “pretty much” equal.

So, I created a little helper function. Or maybe I found it on Google. Look, this was like 8 years ago, so I am sorry if I am missing an attribution…

Code:

public static bool DateEquals(DateTime Date1, DateTime Date2)
        {
            Date1 = new DateTime(
                Date1.Ticks – (Date1.Ticks % TimeSpan.TicksPerSecond),
                Date1.Kind
                );
            Date2 = new DateTime(
                Date2.Ticks – (Date2.Ticks % TimeSpan.TicksPerSecond),
                Date2.Kind
                );
            return Math.Abs(Date1.Subtract(Date2).TotalMilliseconds) <= 10;
        }

So that’s it. What if I WAS designing a missile guidance system and needed the 100 nanosecond precision? I found a nice suggestion here. In his blog, Steve Gobold suggests converting the C# datetimes to ticks, and storing them in the DB as BIGINTs. I like it. It would require a lot of re-factoring of my code, and perhaps I will tackle that someday, but there it is.

I hope this helps someone else!

Advertisements