DATEDIFF() or BETWEEN for Date Ranges in SQL Queries

Question

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

Plauen

"the BETWEEN method in SQL is somewhat unreliable" - what do you mean? – Aboveground

IN this case you should opt for between, Since it will consider the Indexes while scaning, but in DATEDIFF the indexes are not considered – Rance

@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 – Plauen

perhaps they were trying to refer to the fact that column has a time portion? That's very different to being 'unreliable" – Aboveground

@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 – Plauen

If not unreliable it is at least considered evil by some. What do BETWEEN and the devil have in common? – Shutt

Answer 1

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'

Shutt

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? – Plauen

@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. – Shutt

Brilliant, this will be the reason I was instructed not to use it. Thank you very much :-) +1 – Plauen

Answer 2

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.

Scintillation