compare two datetime values from SQL Server with c#

16,648

Solution 1

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"

Solution 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:

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

Solution 3

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.

Solution 4

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.

Solution 5

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.

Share:
16,648
Admin
Author by

Admin

Updated on June 18, 2022

Comments

  • Admin
    Admin almost 2 years

    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#