compare two datetime values from SQL Server with c#

c#, comparison, datetime

Solution

Beware when comparing DateTimes generated within C#. The DateTime struct in C# has more precision than the datetime1 type in SQL Server. So if you generate a DateTime in C# (say from `DateTime.Now`), store it in the database, and retrieve it back, it will most likely be different.

For instance, the following code:

using(SqlConnection conn = new SqlConnection("Data Source=.;Integrated Security=SSPI"))
using(SqlCommand cmd = new SqlCommand("SELECT @d", conn)){
    DateTime now = DateTime.Now;
    cmd.Parameters.Add(new SqlParameter("@d", now));
    conn.Open();
    DateTime then = (DateTime)cmd.ExecuteScalar();
    Console.WriteLine(now.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
    Console.WriteLine(then.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
    Console.WriteLine(then - now);

}

returns the following sample result.

2009.06.20 12:28:23.6115968
2009.06.20 12:28:23.6100000
-00:00:00.0015968

So in this situation, you would want to check that the difference is within a certain epsilon:

Math.Abs((now - then).TotalMilliseconds) < 3

Note that this is not an issue if you're comparing two datetimes retrieved from the database, or a datetime constructed from components with second or larger granularity.

See also: this blog post

1See note about accuracy, where it mentions "Rounded to increments of .000, .003, or .007 seconds"

Problem

Original source