Difference Between Between and <= & >=

Question

I need to take dates between two date intervals.

I Have Tried two queries as below

SELECT * FROM [dbo].[BudgetOffice] BUD
            where  Week      BETWEEN '2015-08-03' AND '2015-08-03'.

 SELECT * FROM [dbo].[BudgetOffice] BUD
            where  Week      >= '2015-08-03' AND Week <= '2015-08-03'.

Both Returns same results. I thought that

between

will return only the values lies in between the input values and exclude the input values. Is that correct?. If both are same which one more feasible with performance.?

[between](https://msdn.microsoft.com/en-us/library/ms187922.aspx) - *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.* As for performance, you can check the execution plan and you will find that both yield the same execution plan. — ughai, Sep 04 '15 at 11:29
There are some issues around using between operator with date values, Aaron Bertrand has explained it in his article [`What do BETWEEN and the devil have in common?`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) definitely worth a read. — M.Ali, Sep 04 '15 at 11:39
@M.Ali So I guess I overestimated the average programmer's skill around datetime when I assumed that generating reports on a monthly basis would use sth. like `SELECT * FROM data GROUP BY YEAR(datetime),MONTH(datetime)`... — Alexander, Sep 04 '15 at 11:50

score 0 · Accepted Answer · edited May 23 '17 at 12:29

Consider snippet:

SELECT * FROM list_of_integers_from_1_to_100 AS table
WHERE table.number BETWEEN 2 AND 80;

This method is by far more readable than "table.number >= 2 AND table.number <= 80". BETWEEN will return all values which are between 2 and 80 (including both numbers) which is effectively the same.

Performance wise, as far as I know, there is no major differences.

There are couple of nifty things to do with BETWEEN that are simpler than using operators. Consider:

table.number NOT BETWEEN 2 AND 80

And:

table.number < 2 AND table.number > 80

Which one is easier on the first glance? I vote for the first one, but in the end it is completely your choice which one to use.

Also, as pointed out in top answer in this question, BETWEEN can help you avoid unnecessary evaluations. I will copy-paste snippet from that topic:

SELECT  AVG(RAND(20091225) BETWEEN 0.2 AND 0.4)
FROM    t_source;

---
0.1998

SELECT  AVG(RAND(20091225) >= 0.2 AND RAND(20091225) <= 0.4)
FROM    t_source;

---
0.3199

Difference Between Between and <= & >=

1 Answers1