CODEWITHSUNDEEP
mysqlsqlsql-serverdatediff
Ben Carey
Ben Carey

Reputation: 16968

DATEDIFF() or BETWEEN for Date Ranges in SQL Queries

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

Upvotes: 6

Views: 12748

Answers (2)

Darrel Lee
Darrel Lee

Reputation: 2470

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.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

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'

Upvotes: 6

Related Questions