6

I have recently been informed that the use of the BETWEEN method in SQL is somewhat unreliable, and I should therefore be using DATEDIFF(). However, another programmer has informed me this is not the case and the BETWEEN method works brilliantly in all cases as long as the date is formatted correctly.

Please could someone settle this debate by stating which method is better and why?

At the moment my date range SQL looks like this:

DATEDIFF(d,'01-Jan-1970',SIH.[Something_Date]) >= 0 AND DATEDIFF(d,'01-Jan-2013',SIH.[Something_Date]) <= 0

However, I would much rather write it like this if I can be sure it is reliable:

SIH.[Something_Date] BETWEEN '01-Jan-1970' AND '01-Jan-2013'

In this particular case I am using MsSQL, however, I have tagged MySQL as I would like to know if this applies here as well

Ben Carey
  • 16,540
  • 19
  • 87
  • 169
  • "the BETWEEN method in SQL is somewhat unreliable" - what do you mean? – Mitch Wheat Jan 04 '13 at 08:50
  • IN this case you should opt for between, Since it will consider the Indexes while scaning, but in DATEDIFF the indexes are not considered – Sashi Kant Jan 04 '13 at 08:51
  • @MitchWheat That is what I was informed, he did not state why it was unreliable, he just said that I should not be using it – Ben Carey Jan 04 '13 at 08:51
  • perhaps they were trying to refer to the fact that column has a time portion? That's very different to being 'unreliable" – Mitch Wheat Jan 04 '13 at 08:52
  • @MitchWheat Quite possibly, either way he instructed me to use this method. The reason I am reserved is the `BETWEEN` method is so much simpler, and probably faster... So I am unsure which method to go with – Ben Carey Jan 04 '13 at 08:54
  • 1
    If not unreliable it is at least considered evil by some. [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Mikael Eriksson Jan 04 '13 at 09:04

2 Answers2

6

Your two queries are not equivalent. The datediff version will include all values from 01-Jan-2013 regardless of time while the between version will include only the rows on 01-Jan-2013 where time is 00:00:00.

If you check against the range and don't do any calculations on the column, your query will be able to use a index on Something_Date and at the same time include all values from 01-Jan-2013 regardless of the time part.

where
  SIH.[Something_Date] >= '19700101' and
  SIH.[Something_Date] < '20130102'
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Quite confused, are you saying that if a field has a time specified that is not 00:00:00 then `BETWEEN` will not find it? – Ben Carey Jan 04 '13 at 09:04
  • 3
    @BenCarey Yes that is what I am saying. When developers find that out they usually do a between to the "next" day instead with side effect that they include rows with time `00:00:00` from the next day. And when they figure that out they write the between to use `20130101 23:59:59` and misses out on the last second. And when they figure that out they try `20130101 23:59:59.999` just to find out that a `datetime` value has a precision of 3 ms so it is rounded up to `20130102 00:00:00`. By then they hopefully stop using between even if `20130101 23:59:59.997` does work, at least for a `datetime`. – Mikael Eriksson Jan 04 '13 at 09:08
  • Brilliant, this will be the reason I was instructed not to use it. Thank you very much :-) +1 – Ben Carey Jan 04 '13 at 09:10
0

The reason to avoid BETWEEN is there is no way to use it correctly if your date values can include a time component. If time is included, then comparison to the upper bound must use a strictly less than comparison which rules out BETWEEN.

On the other hand, if you know that the date values never include a time component or if you have a convenient way of removing the time component, then I much prefer to use BETWEEN because it seems more readable.

In Oracle SQL, I can use the TRUNC function to remove the time component (It can also truncate to 'MONTH' or 'YEAR').

So I can write:

where TRUNC(date_col) between '01-JAN-2021' and '31-DEC-2021'

And be sure the test won't miss dates on the 31st that also have time component.

I am under the impression that this use of TRUNC (and ROUND) may be unique to Oracle. I do not know if there are equivalents in mySQL or T-SQL. (I seem to remember having to use datediff tricks to accomplish what TRUNC does.

Without TRUNC (or equivalent) you must use:

where '01-JAN-2021' <= date_col and date_col < '01-JAN-2022'

Note the comparison to the upper bound must be strictly less than.

This is shorter, very readable, probably more efficient, and guaranteed to work.

But I still prefer to use TRUNC and BETWEEN because the logic is cleaner. (I messed up the second example in two different ways before I got it right).

I doubt that there's much of a difference in efficiency, and even if there is in most situations it's probably not an issue. I think readable is more important than efficiency (most of the time).

But of course, Correctness trumps everything. So if you don't have a convenient way to remove the time component or must include the time component, then you can't use BETWEEN to accurately test datetime values.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Darrel Lee
  • 2,372
  • 22
  • 22