Emulating impact of SQL storage on C# DateTime-CodePudding

I have uncovered a unfortunate side-effect that storing and rehydrating a C# DateTime from a SQL Server datetime column alters it slightly, so that the equality operator is no longer valid.

For example, using EF:

FileInfo fi = new FileInfo("file.txt");
Entity.FileTimeUtc = fi.LastWriteTimeUtc;

we find that

(Entity.FileTimeUtc == fi.LastWriteTimeUtc)  // true

but if we save that entity and then reload it from SQL Server, we find that

(Entity.FileTimeUtc == fi.LastWriteTimeUtc)  // false

I understand that a process of rounding has happened here (if only by a few milliseconds) due to differing internal storage formats between the .NET DateTime and the SQL datetime.

What I am looking for is a process that will reliably emulate this conversion, and align native DateTime values to those which have been stored and rehydrated from a SQL datetime field, to make the equality test valid again.

CodePudding user response：

That is because SQL Server's DateTime type counts time in 3- and 4-millisecond "ticks", with some very odd "rounding" rules.

See my answer to the question, "How does SqlDateTime do its precision reduction?" for details on exactly what those rounding rules are.

That will allow you to do the exact same conversion in .Net.

Also, I believe that if converting your C#/.Net DateTime values to a System.Data.SqlTypes.SqlDateTime will do the same conversions (but I can't swear to that — it's been a while since I had to wrangle with that).