CODEWITHSUNDEEP
c#asp.netc#-4.0
user3587180
user3587180

Reputation: 1397

DateTime.Compare is not working as expected

I'm inserting customers the following way.

DateTime d = DateTime.Now;

foreach(Customer cus in CustomerList)
{
    cus.EntryDate = d;
}

SaveToDatbase(CustomerList);

Date is getting saved successfully with the correct entry date, but however when I retrieve data back and compare it with variable d, it doesn't match.

foreach(Customer cus in GetFromDatabase())
{
    Response.Write(DateTime.Compare(cus.EntryDate, d));
}

The output is 1 instead of 0. I checked the inserted data and it matches d variable value with milliseconds. Date is stored in sql server database. One thing I realized is if I reset milliseconds to 0 for cus.EntryDate and d, output is 0. What am I doing wrong here?

Upvotes: 2

Views: 1504

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74267

SQL Server's datetime value has a precision of 1 milicsecond, but an accuracy of [approximately] 3.33ms. Further, it doesn't actually "round" the millisecond count. Its "rounding" algorithm is...byzantine, to be kind. But it is, at least, documented. My answer to the question, "Round .NET DateTime milliseconds, so it can fit SQL Server milliseconds", which you can find at https://stackoverflow.com/a/5318635/467473, will

  • point you to the documentation for how the conversion is performend, and
  • has an datetime extension method that will convert a CLR System.DateTime value to its corresponding SQL Server datetime equivalent.

One should note that to due to the precision/accuracy difference between the two representations, this is not a round trip conversion.

Upvotes: 1

Dai
Dai

Reputation: 155145

SQL Server's datetime type has a lower resolution than .NET's. As you're using DateTime.Now directly (without rounding it to the nearest second) you'll see differences in the milisecond or tick (100 nanosecond) portions.

According to MSDN, the datetime type in SQL Server has a resolution of approximately 3 miliseconds (specifically: 2015-08-18 15:49:10.000 -> 2015-08-18 15:49:10.003 -> 2015-08-18 15:49:10.007 -> 2015-08-18 15:49:10.010) whereas .NET's DateTime type has a resolution of 100 nanoseconds.

If you want to preserve some kind of equality, I suggest rounding DateTime to the nearest second before inserting into the database:

DateTime now = DateTime.Now;
now = new DateTime(
    now.Ticks - ( now.Ticks % TimeSpan.TicksPerSecond ),
    now.Kind
);

foreach(Customer cus in customerList) cus.EntryDate = now;

Upvotes: 5

Related Questions