将 SQL Server 中的两个日期时间值与 C# 进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1021755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
compare two datetime values from SQL Server with c#
提问by
i want to know how compare two datetime values one who is retreived from sql database and the other is the current one with c#
我想知道如何比较两个日期时间值，一个是从 sql 数据库中检索的，另一个是使用 c# 的当前值
回答by Joseph
You can use the DateTime.CompareTomethod.
您可以使用DateTime.CompareTo方法。
Usage is like this:
用法是这样的：
firstDateTime.CompareTo(secondDatetime);
and returns an int as a result which indicates
并返回一个 int 作为结果表明
Less than zero - This instance is earlier than value.
Zero - This instance is the same as value.
Greater than zero - This instance is later than value.
小于零 - 此实例早于值。
零 - 此实例与值相同。
大于零 - 此实例晚于值。
回答by ChrisF
Assuming that you want to check that the two DateTimes are equivalent there's this way:
假设您要检查两个 DateTimes 是否等效，请使用以下方法：
TimeSpan span = dateTime2 - dateTime1;
if (span == TimeSpan.Zero)
{
// The times are the same
}
You will need to convert the System.Data.SqlTypes.SqlDateTimeto System.DateTimefirst of course, as echosca points out in his answer.
您需要将转换System.Data.SqlTypes.SqlDateTime到System.DateTime第一，当然，作为echosca指出了他的答案。
Though there should some allowed rounding error (in the millisecond range say), as these are likely to be real-world derived values, as the simple equality wouldn't be good enough. You'd need something like this:
虽然应该有一些允许的舍入误差（在毫秒范围内），因为这些很可能是现实世界的派生值，因为简单的相等性不够好。你需要这样的东西：
if (Math.Abs(span.TotalMilliseconds) < 10.0)
{
// The times are within the allowed range
}
If you just want to compare whether one date is before or after another use the DateTime.CompareTomethod as others have suggested.
如果您只想比较一个日期是在另一个日期之前还是之后，请使用DateTime.CompareTo其他人建议的方法。
回答by Noldorin
The standard comparison operators (e.g. equality, less than, greater than) are overloaded for the DateTimetype. So you can simply perform tests such as the following:
该类型的标准比较运算符（例如相等、小于、大于）被重载DateTime。因此，您可以简单地执行如下测试：
var foo = DateTime.Parse("01/01/1900");
var bar = DateTime.Now;
var test1 = foo == bar; // false
var test2 = foo != bar; // true
var test3 = foo < bar; // true
var test4 = foo > bar; // false
回答by J.W.
回答by Jeff Meatball Yang
When retrieved from the database, you should be able to use a SqlDataReader to cast to the correct .NET type. (or use DataTable/DataSet, which automatically does this).
从数据库中检索时，您应该能够使用 SqlDataReader 转换为正确的 .NET 类型。（或使用自动执行此操作的 DataTable/DataSet）。
SqlDataReader dr = cmd.ExecuteReader();
DateTime dt = dr.GetDateTime(dr.GetOrdinal("someDateTimeColumn"));
then you can compare normally:
那么你可以正常比较：
DateTime otherDate = DateTime.Now;
int compResult = dt.CompareTo(otherDate);
if(compResult > 0) { Console.Write("dt is after otherDate"); }
else if(compResult < 0) { Console.Write("dt is before otherDate"); }
else { Console.Write("dt is equal to otherDate"); }
回答by Andrija
DateTime struct overrides GreterThen, GreaterThenOrEqual, LesserThen, LesserThenOrEqual operater, Equalty operater.
DateTime 结构覆盖 GreterThen、GreaterThenOrEqual、LesserThen、LesserThenOrEqual 运算符、Equalty 运算符。
DateTime dateTime1, dateTime2;
dateTime1 = DateTime.Now;
dateTime2 = //set value from database;
// all this operations are legal
if(dateTime1 == dateTime2){}
if(dateTime1 > dateTime2){}
if(dateTime1 < dateTime2){}
回答by ehosca
System.Data.SqlTypes.SqlDateTime and System.DateTime use different underlying structures to represent dates.
System.Data.SqlTypes.SqlDateTime 和 System.DateTime 使用不同的底层结构来表示日期。
SqlDateTime represents the range January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds
SqlDateTime 表示范围从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 3.33 毫秒
DateTime(.NET Framework type) represents the range between Jan 1, 0001 to Dec,31 9999 to the accuracy of 100 nanoseconds
DateTime(.NET Framework 类型) 表示从 Jan 1, 0001 到 Dec, 31 9999 到 100 纳秒精度的范围
You should be careful of these boundaries when comparing dates. One tactic to alleviate problems in comparisons could be to cast everything to System.DateTime and then perform the compare.
在比较日期时，您应该注意这些界限。缓解比较问题的一种策略是将所有内容都转换为 System.DateTime，然后执行比较。
You can use the Value property of the SqlDateTime struct (which returns a System.DateTime) to do the comparison elegantly and without explicit casting.
您可以使用 SqlDateTime 结构（它返回 System.DateTime）的 Value 属性来优雅地进行比较并且无需显式转换。
You might find this articleinformative.
您可能会发现这篇文章内容丰富。
回答by Darth Continent
I hope you find this article (DATEDIFF Function Demystified) useful, although it's specific to datetime in SQL, it's helpful for understanding how it's handled on the database side.
我希望这篇文章 ( DATEDIFF Function Demystified) 对您有用，尽管它特定于 SQL 中的日期时间，但它有助于理解它在数据库端的处理方式。
回答by P Daddy
Beware when comparing DateTimes generated within C#. The DateTimestruct in C# has more precision than the datetime1type 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.
比较在 C# 中生成的日期时间时要小心。C# 中的DateTime结构比SQL Server 中的datetime 1类型具有更高的精度。因此，如果您在 C# 中生成 DateTime（例如 from DateTime.Now），将其存储在数据库中，然后检索回来，它很可能会有所不同。
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:
因此，在这种情况下，您需要检查差异是否在某个 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"
1请参阅关于准确性的说明，其中提到“四舍五入到 .000、.003 或 0.007 秒的增量”
