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.
例如,以下代码:
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);
}
返回以下示例结果。
2009.06.20 12:28:23.6115968
2009.06.20 12:28:23.6100000
-00:00:00.0015968
因此在这种情况下,您需要检查差异是否在某个 epsilon 范围内:
Math.Abs((now - then).TotalMilliseconds) < 3
请注意,如果您要比较从数据库检索的两个日期时间,或者比较从具有第二或更大粒度的组件构造的日期时间,这不是问题。
也可以看看:这篇博文
1See note about accuracy, where it mentions "Rounded to increments of .000, .003, or .007 seconds"