SYSK 72: Fast Comparison of Dates

It’s well known that comparing variables of DateTime data type, whether in .NET or in SQL, is not the fastest operation…  So, if you have a table with EffectiveDate and ExpirationDate columns, and you need to get only rows where EffectiveDate >= Now and Now < ExpirationDate, you may want to do something to expedite this query.  Of course, column indexes are an option, and may be a good one; but is there anything else that can be done?

 

We know that numbers (e.g. integers) are fast to do comparison on.  So, you may want to store the dates as integers.  In .NET, the DateTime type has two great methods just for that – ToBinary and FromBinary.

Try this:

DateTime d1 = DateTime.Now;

long l = d1.ToBinary();

DateTime d2 = DateTime.FromBinary(l);

 

In SQL, I haven’t found a way to preserve time, but a simple CAST(getdate() as int) and back CAST(n as DateTime) will do the conversion to an integer and back to date (time will be zeroed out)…  In most business cases, this is sufficient.

 

Of course, the drawback of this solution is that anytime you want to display the effective/expiration dates as dates, you’ll have do to the cast… But since, most likely, you’re going to do that less frequently than programmatically finding non-expired, effective rows, in my opinion, it’s worth it.

Comments

• Anonymous
  February 28, 2006
  Why would comparing two dates in .NET be slow?
  
  Internally DateTime uses an int64 to record the ticks - it is these that are compared.
  
  FromBinary and ToBinary are only available in .NET 2.0 - .Ticks is available in both .NET 1.1 and 2.0.  The only issue to be careful of is time zones.
  
  [)amien
• Anonymous
  March 01, 2006
  I can see where my post is a bit misleading.  You're right that DateTime in .NET is internally stored as a 64-bit unsigned integer.  For those who are interested, bits 01-62 store the value of 100-nanosecond ticks where 0 represents 1/1/0001 12:00am, up until the value 12/31/9999 23:59:59.9999999; bits 63-64 store a four-state value that describes the DateTimeKind value of the date time, with a 2nd value for the rare case where the date time is local, but is in an overlapped daylight savings time hour and it is in daylight savings time. This allows distinction of these otherwise ambiguous local times and prevents data loss when round tripping from Local to UTC time.
  
  So, .NET to .NET DateTime comparison is not slow (although I'd expect it to be negligibly slower that int to int).  Having said that, the point of the blog was about storing DateTime as number in SQL for better performance.  To get a number from DateTime, one can use ToBinary method; to convert it back to date for display purposes (e.g. admin tool that allows somebody to maintain the data, including effective/expiration dates) you’ll use FromBinary.
  
  I hope this clarifies what I was trying to convey in the original post :)