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.