CODEWITHSUNDEEP
sqlsql-servert-sql
veljasije
veljasije

Reputation: 7092

What is real purpose of BETWEEN clause in SQL?

For proper use of indices, you must make filter predicate as a search argument, so you can't use DATETIME functions in this case.

You need to use <= and >= operands for date and time comparisons, but there is also the BETWEEN clause.

So, recommendations is to use simple arithmetical operands, because BETWEEN has problem with end dates.

Is there any context where BETWEEN is preferable to <= and >=?

Upvotes: 1

Views: 257

Answers (4)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

So, recommendations is to use simple arithmetical operands, because BETWEEN has problem with end dates.

No. BETWEEN doesn't have a problem with end dates (for date and time values) but developers because some people forget that a date and time (the DATETIME data type) value has, also, a time component.

So, writing

SELECT ...
FROM MyTable 
WHERE MyDateTimeColumn BETWEEN '20130101' AND '20130113'

is wrong when you want to show all records between these two dates because '20130113' means '20130113 00:00:00.000' (and not '20130113 23:59:59.997') and BETWEEN '20130101' AND '20130113' means BETWEEN '20130101' AND '20130113 00:00:000.000'.

Some solutions:

1)

SELECT ...
FROM MyTable 
WHERE MyDateTimeColumn BETWEEN '20130101' AND '20130113 23:59:59.997'

2)

SELECT ...
FROM MyTable 
WHERE CONVERT(DATE, MyDateTimeColumn) BETWEEN '20130101' AND '20130113'

3)

DECLARE @StartDate DATETIME = '20120103'
DECLARE @EndDate DATETIME = '20120103'

SET @EndDate = DATEADD(MILLISECOND, -3, DATEADD(DAY, 0, DATEDIFF(DAY, 0, @EndDate)+1))

SELECT @StartDate AS [SD], @EndDate [ED]

SELECT ...
FROM MyTable 
WHERE MyDateTimeColumn BETWEEN @StartDate AND @EndDate

Results:

SD                      ED
----------------------- -----------------------
2012-01-03 00:00:00.000 2012-01-03 23:59:59.997

(1 row(s) affected)

...

Note: DATETIME values are "rounded to increments of .000, .003, or .007 seconds".

Upvotes: 5

BellevueBob
BellevueBob

Reputation: 9618

If you are asking if there is any difference between

where X between 1 and 10

versus

where X >= 1 and X <= 10

then no, there is no difference. It is just a convenient enhancement to the language, one of many that try to make life easier; a BETWEEN expression very clearly expresses the desired result.

It's similar to the COALESCE function, which is just a quicker way to write a CASE expression that checks for NULL values.

Upvotes: 7

Sebastian Meine
Sebastian Meine

Reputation: 11773

BETWEEN gets translated into two separate operation connected with or: a BETWEEN b AND c is equivalent to a >= B AND a <= c. Equivalent in this context means functionally equivalent. It will produce the same behavior in the optimizer and the execution engine.

The problem lies in the fact that the word between in natural languages is fuzzy. If it is meant with or without inclusion of the boundaries depends on a lot of factors that are not always obvious. Fuzzy behavior we need to keep away from programming.

While the T-SQL BETWEEN has a well defined behavior, it is easy to forget which one it is. Because code is read much more often than it is written, it is important to make it as readable as possible. Every time a reader encounters the BETWEEN keyword (s)he has to stop and think about the boundary behavior. That is distracting and also leads to hard to find bugs.

The reason you heard that there is a problem with the end boundary in date calculations is, that that is the case were people get the use of BETWEEN wrong most often.

Upvotes: 1

Oded
Oded

Reputation: 498952

It is beneficial for readability when using numeric types.

If you need to check for an integral column value between two integral values:

theColumn BETWEEN 5 and 25

Upvotes: 1

Related Questions