将SQL Server中的两个日期时间值与c＃进行比较

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#

9 个解决方案

#1

Beware when comparing DateTimes generated within C#. The DateTime struct in C# has more precision than the datetime¹ 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.

在比较C#中生成的DateTime时要小心。 C#中的DateTime结构比SQL Server中的datetime1类型具有更高的精度。因此,如果您在C#中生成DateTime(例如从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.

请注意,如果您要比较从数据库检索的两个日期时间,或者使用具有第二个或更大粒度的组件构建的日期时间,则这不是问题。

#2

The standard comparison operators (e.g. equality, less than, greater than) are overloaded for the DateTime type. 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

#3

You can use the DateTime.CompareTo method.

您可以使用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.

大于零 - 此实例晚于值。

#4

Assuming that you want to check that the two DateTimes are equivalent there's this way:

假设您要检查两个DateTime是否相同,就是这样:

TimeSpan span = dateTime2 - dateTime1;
if (span == TimeSpan.Zero)
{
    // The times are the same
}

You will need to convert the System.Data.SqlTypes.SqlDateTime to System.DateTime first 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.CompareTo method as others have suggested.

如果您只想比较一个日期是在另一个日期之前还是之后,请使用其他人建议的DateTime.CompareTo方法。

#5

You need put the value from sql to C# DateTime object, and then compare them in C#. Here is a link from MSDN on how to do it.

您需要将sql中的值放入C#DateTime对象,然后在C#中进行比较。以下是MSDN上有关如何操作的链接。

#6

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"); }

#7

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类型)表示介于0001 1月1日到12月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结构的Value属性(返回System.DateTime)来优雅地进行比较,而无需显式转换。

You might find this article informative.

您可能会发现这篇文章内容丰富。

#8

DateTime struct overrides GreterThen, GreaterThenOrEqual, LesserThen, LesserThenOrEqual operater, Equalty operater.

DateTime结构覆盖GreterThen,GreaterThenOrEqual,LesserThen,LesserThenOrEqual operater,Equalty operater。

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){}

#9

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中的datetime,但它有助于理解它在数据库端的处理方式。

#1