2

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?

Community
  • 1
  • 1
Ksice
  • 3,277
  • 9
  • 43
  • 67

3 Answers3

5

Subtract one from the other & check the ticks of the resulting TimeSpan to be within acceptable limits for the difference in tick length

PaulF
  • 6,673
  • 2
  • 18
  • 29
4

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • **Word of warning**: this does not seem to always work. I tested 100 `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). – Paul Oct 07 '16 at 16:12
0

if you ignore millisecond difference than you can try this

Select * from MyTable DATEADD(ms, -DATEPART(ms, endTime), endTime) = @value
sangram parmar
  • 8,462
  • 2
  • 23
  • 47