CODEWITHSUNDEEP
c#sqldatetimeroundingprecision
user2864740
user2864740

Reputation: 61875

Round DateTime using SQL DATETIME rules

Say there is some code that compares a DateTime object with a DateTime object that has been saved and returned from SQL Server, stored in a DATETIME object.

The comparison is done on hh:mm:ss-equality, but it is possible that SQL Server 'changes' the second component when saved which makes the comparisons fail ~1.5/1000 of the time or so.

This is because SQL Server will round/truncate this value when saved in the database as a DATETIME value:

datetime values are rounded to increments of .000, .003, or .007 seconds..

Is there a (standard) C#/.NET function that does the same rounding?

The primary goal of this question is to normalize the value prior to saving, for use in comparisons. That is, F(original) == F(saved) should always be true.

The final goal overall is to ensure the values are saved 'within the correct second', such that hh:mm:01.999 is stored as hh:mm:01.997. This would allow the hh:mm:ss-equality comparisons to be reliable regardless of if done to the original DateTime values or restored values. In this case, original.Second == F(original).Second should always be true as well.

For better or worse, one widely-used assumption is the comparison is done per hh:mm:ss, so a simple epsilon-compare of 2 milliseconds is out; although I wouldn't be opposed to a strongly-argued for comparison function that might also solve the final goal.

Upvotes: 1

Views: 1117

Answers (1)

user743382
user743382

Reputation:

The SqlDateTime structure stores date/time values in the same way as SQL Server's datetime type. It provides conversions from and to the .NET DateTime type, and rounds when the conversion is performed.

Note that you have contradictory requirements in your question. You say you want the same rounding as SQL Server. You also say you want the rounding to never change the "second" component. You can't have it both ways. If you need the "second" component to not change, you may need to implement that yourself. You can check after the conversion has been done whether the second changed, and then restore it, or you may implement the conversion yourself to always round down.

Upvotes: 4

Related Questions