But I Need To Compare Two Date Columns In My Where Clause

Tell It To The Judge

A common dilemma is when you have two date columns, and you need to judge the gap between them for something.

For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.

You’re not left with many good ways to write the query to take advantage of indexes.

Let’s have a look-see.

Iron Mask

We’re gonna skip the “aw shucks, this stinks without an index” part.

Because it still stinks with an index. Check it out.

Why would you?

Though we have a predicate, and an index on both columns, we don’t have anything to seek to.

Why? Because our predicate isn’t on anything that the index is keeping track of.

Indexes don’t care how many years, months, days, hours, minutes, seconds, milliseconds, or microseconds difference there are between these two columns.

That’d be a really cool kind of index to have for sure, but insert a shrug that fills your screen the way dark matter fills the universe here.

All we got is workarounds.

Another For Instance

We can use a computed column:

The result is something we can seek to.

How could you?

Which is probably the type of plan that you’d prefer.

Thanks for reading!

8 thoughts on “But I Need To Compare Two Date Columns In My Where Clause”

  1. For the sake of completeness: you could add a “WHERE sadness > 9” to your CREATE INDEX statement, if you only query for those rows (and nearly never for <= 9). I guess, that only maybe 5 % of the orders will take more than 9 hours to ship, so this would reduce the size of the index by 95 %.

    Important: if you filter for a @variable in your query, a filtered index can only be used, if you specify an OPTION(RECOMPILE) too (which could have some drawbacks, if you run this VERY often (every few seconds) and not only a few times per day).

    1. For the sake of completeness, you should test these things out 😉

      Msg 10609, Level 16, State 1, Line 5
      Filtered index ‘sadness’ cannot be created on table ‘dbo.Posts’ because the column ‘despair’ in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

      1. Sorry, I forgot this, but you could filter for WHERE DATEDIFF(YEAR, CreationDate, LastActivityDate) > 9.

        With a little bit luck (would need to test it but have no fitting PC at the moment) it would use the index, even if we filter for despair > 9, otherwise we had to modify our query.

      2. Really? When will this product finally get out of beta phase? Is it too hard do add a row to a b-tree when the value satisfies the index condition, or remove it accordingly?

  2. Hi Erik,
    always enjoy reading your posts! And thanks a lot for the all the time and effort you take to enlighten your colleagues around the world!
    Just a quick Question: since when is it no longer required to specify a computed column as ‘persisted’ for allowing it to be indexed?

    1. Hi! Thanks — happy to be reaching people! Funny question: it’s never really been necessary.

      Computed columns don’t have to be persisted to index them, or have statistics on them.

Leave a Reply

Your email address will not be published. Required fields are marked *