Damir's Corner.

DateTime Value Might Change When Saved to Database

DateTime can be a tricky data type to deal with. Not only is there daylight saving time and different time zones to keep in mind but also the range and precision can vary in different systems. You are probably already aware of some differences between .NET framework's DateTime structure in Transact-SQL's datetime data type.

Most notable is certainly a different minimum value that can be expressed:

• In .NET framework all values between January 1st 1 and December 31st 9999 are supported.
• In Transact-SQL only values between January 1st 1753 and December 31st 9999 are supported.

This usually shouldn't be a problem unless you are working with historic data. Setting proper minimum value in the user interface is not be a bad idea nevertheless.

You might not be aware of the difference in precision between these two data types, though. In some edge cases this can cause strange and unpredictable behavior. Let's take a look at the following example (based on an issue that had me mystified for some time):

using (var context = new TestEntities())
{
    var task = context.Tasks.Single(t => t.Id == taskId);
    task.CompletedAt = DateTime.Now;
    timestamp = task.CompletedAt.Value;
    context.SaveChanges();
}

using (var context = new TestEntities())
{
    var task = context.Tasks.Single(t => t.Id == taskId);
    Assert.GreaterOrEqual(timestamp, task.CompletedAt);
}

The assertion in the above code will occasionally fail with a message similar to this one:

SampleTest has failed:
  Expected: greater than or equal to 2011-12-26 19:04:28.313
  But was:  2011-12-26 19:04:28.312

If you're not familiar with entity framework, here's what's going on:

• In the first code block a DateTime value is stored to the database.
• In the second code block that same value is reloaded from the database.
• The value from the database is compared with the one stored in a local variable the whole time.

The strange behavior is caused by the fact that milliseconds get rounded in the database: to increments of .000, .003, or .007 seconds, as it is stated in the documentation. The DateTime value can therefore be different when reloaded from the database, hence the occasional failed assertion. While the problem might be fairly obvious in the simplified example above, it can be much more difficult to pinpoint in a larger chunk of code where there could be other reasons for the values to differ. Even more so if you're not even aware of the rounding.

Knowing this might save you a couple of hours once.