0

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.

stephen
  • 303
  • 1
  • 8
  • 7
    Use `datetime2` instead of `datetime` and you will store an accurate datetime. – Dale K Jan 24 '22 at 23:03
  • 1
    The resoluion of `datetime` is about 3ms. So you could also just check that the difference between the two is under 10ms. – David Browne - Microsoft Jan 24 '22 at 23:11
  • 1
    datetıme2 and datetimeoffset would be safer. If you truncate milliseconds then it would be the best. You can also save unix tımestamp which would be a big integer and doesn't have milliseconds problem. – Cetin Basoz Jan 24 '22 at 23:12

1 Answers1

1

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).

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • Thanks Nicholas. This and your previous answer are very helpful. I don't need millisecond accuracy. If I preround these values to the nearest second, do you know if the .NET and SQL datetimes will interconvert faithfully? But perhaps it is just safer to convert to .NET ticks for storage and querying? – stephen Jan 25 '22 at 01:21
  • If you round (or truncate) to the nearest second before inserting into the SQL Server database, you should be fine — SQL server won't have any fractional seconds to mess up. – Nicholas Carey Jan 25 '22 at 02:27
  • Thanks, my feeling is to move towards storing ticks for this anyway. I'm really not doing any operations related to time as a continuous medium. I'm just wanting to know "has this file changed since I last saw it?" which needs precise and reliable equality/inequality only. – stephen Jan 25 '22 at 02:51