compare two datetime values from SQL Server with c#
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.
Admin
Updated on June 18, 2022Comments
-
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#