compare two datetime values from SQL Server with c#

Question

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#

Answer 1

You can use the DateTime.CompareTo method.

Usage is like this:

firstDateTime.CompareTo(secondDatetime);

and returns an int as a result which indicates

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.

Answer 2

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

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.

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.

Answer 3

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:

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

Answer 4

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.

Answer 5

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

Answer 6

DateTime struct overrides 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){}

Answer 7

System.Data.SqlTypes.SqlDateTime and System.DateTime use different underlying structures to represent dates.

SqlDateTime represents the range January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds

DateTime(.NET Framework type) represents the range between Jan 1, 0001 to Dec,31 9999 to the accuracy of 100 nanoseconds

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.

You can use the Value property of the SqlDateTime struct (which returns a System.DateTime) to do the comparison elegantly and without explicit casting.

You might find this article informative.

Answer 8

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.

Answer 9

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.

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

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