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:
- Create an object and set all fields, including a date field.
- Save the object to a database table, again, the database table has a SQL datetime field corresponding to the C# DateTime property.
- Instantiate a new object.
- Load from the database into the new object.
- Compare the objects, including the DateTime field.
- 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…
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!