Date comparison BETWEEN vs < >

Question

I have a question about the performance of the BETWEEN keyword vs. using the < and > operators to compare the upper and lower bound dates.

Example:

WHERE EncDate BETWEEN '2010-04-30' AND GETDATE() - 1

WHERE EncDate > '2010-04-29' AND EncDate < GETDATE()

Both queries above should output the exact same number of rows, however, am I correct in assuming that BETWEEN keyword performs an inclusive (>= or <=) comparison behind the scenes which is not as performant as doing a < or > comparison from the second query?

Try it, run both with "show execution plan" on and see which performs better, — Dale K, Oct 30 '19 at 22:47
Does this answer your question? [SQL : BETWEEN vs <= and >=](https://stackoverflow.com/questions/1630239/sql-between-vs-and) — GSazheniuk, Oct 30 '19 at 23:16

score 0 · Answer 1 · answered Oct 30 '19 at 23:15

According to MSDN: BETWEEN

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

So you are right, it is inclusive.

Regarding the first part of your question about performance, I believe BETWEEN is just an alias for syntax of using >= and <=. It is just to simplify and make query more readable.

My assumption is based on following threads:

sql: BETWEEN v1 and v2

SQL: BETWEEN vs <= and >=

Gordon Linoff · Answer 2 · 2019-10-31T01:20:36.480

These two are not the same!

WHERE EncDate BETWEEN '2010-04-30' AND GETDATE() - 1

WHERE EncDate > '2010-04-29' AND EncDate < GETDATE()

They will produce the same result set if EncDate is a date with no time component. Otherwise, they are different.

The general recommendation is to use:

WHERE EncDate >= '2010-04-30' AND 
      EncDate < CONVERT(DATE, GETDATE())

This has the same meaning regard of whether or not there is a time component.

I am pretty sure that from an optimization perspective, these are exactly the same. When working with databases, something as minor as comparing times is generally going to have no impact on performance -- the expensive operations are moving the data around.

Aaron Bertrand has a very good discussion on using BETWEEN with date/time data types, called What Do Between And The Devil Have In Common.

Date comparison BETWEEN vs < >

2 Answers2