This was born from my previous question
I have a DateTime in c#.
Then this value is inserted to database.
After that select this value and compare that date is the same as it was in the beginning.
What is the best way to do this? Since SQL datetime has different ticks, DateTime from the first step will not be the same as SQL DateTime (row["MyDate"])
How to compare them?
You can use the SqlDateTime structure.
DateTime now = DateTime.Now;
SqlDateTime sqlNow = new SqlDateTime(now);
bool equal = now == sqlNow.Value; // false
So if you have a DateTime and want to know if it's equal to a DB-DateTime use:
Assert.Equal(dbEndTime, new SqlDateTime(endTime).Value); // true
SqlDateTime:
Represents the date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds to be stored in or retrieved from a database. The SqlDateTime structure has a different underlying data structure from its corresponding .NET Framework type, DateTime, which can represent any time between 12:00:00 AM 1/1/0001 and 11:59:59 PM 12/31/9999, to the accuracy of 100 nanoseconds. SqlDateTime actually stores the relative difference to 00:00:00 AM 1/1/1900. Therefore, a conversion from "00:00:00 AM 1/1/1900" to an integer will return 0.
DateTimes for equality this way (store 100, retrieve and compare against the in-memory ones) and, across different runs, I'd always get a few DateTimes falsely detected as "non-equal" (and yes, their ticks were actually different). I am running against the (LocalDB)\v11.0 provider, so maybe not all SQL providers use SqlDateTime to convert date/times? Anyway, I opted for checking that the absolute difference > 3.33 ms (similar to what @PaulF suggests below).Subtract one from the other & check the ticks of the resulting TimeSpan to be within acceptable limits for the difference in tick length
if you ignore millisecond difference than you can try this
Select * from MyTable DATEADD(ms, -DATEPART(ms, endTime), endTime) = @value
