QUESTION: Why is SQL's BETWEEN inclusive?

ANSWER: Because the SQL language designers made a poor design decision, in that they failed to deliver syntax that would allow developers to specify which of the 4 variants of BETWEEN (closed, semi-open-left, semi-open-right, or open) they'd prefer.

RECOMMENDATION: Unless/until the SQL standard is amended, don't use BETWEEN for dates/times . Instead get into the habit of coding DATE range comparisons as independent conditions on the start and end boundaries of your BETWEEN range. This is a bit verbose, but will leave you writing conditions that are intuitive (thus less likely to be buggy) and clear to the database optimizers, allowing for optimal execution plans to be determined and indexes to be used.

For example, if your query is accepting an input day specification and should return all records which fell on that date, you'd code as:
WHERE DATE_FIELD >= :dt AND DATE_FIELD < :dt+1

Trying to write the logic using BETWEEN risks performance issues and/or buggy code. Three common missteps:

1) WHERE DATE_FIELD BETWEEN :dt AND :dt+1

This is almost certainly a bug - user expects to see only records for a particular date, yet one day will wind up with a report containing records from 12:00 a.m. of the next day.

2) WHERE TRUNC(DATE_FIELD) = :dt

Gives right answer, but applying the function to DATE_FIELD will render most indexing / statistics useless (though sometimes DBAs will try to help by adding function-based indexes to the date fields - still burning up man-hours and disk space and adding overhead to IUD operations on the table)

3) WHERE EVENT_DATE BETWEEN :dt AND :dt + 1-1/24/60/60

Tom Kyte, Oracle guru extraordinaire, recommends this less-than-elegant (IMO) solution. Works great until you're spending all day to find that "1-1/24/06/60" in a query that gives incomplete results... or until you accidentally use it on a TIMESTAMP field. Plus, it's a bit proprietary; compatible with Oracle's DATE data type (which tracks to the second), but needs to be adjusted to the DATE/TIME precision of different database products.

SOLUTION: Petition the ANSI SQL committee to enhance the SQL language specs by modifying the BETWEEN syntax to support specification of alternatives to the CLOSED/INCLUSIVE default. Something like this would do the trick:

expr1 BETWEEN expr2 [ INCL[USIVE] | EXCL[USIVE] ] AND expr3 [ INCL[USIVE] | EXCL[USIVE] ]

Consider how easy it becomes to express WHERE DATE_FIELD BETWEEN :dt INCLUSIVE AND :dt+1 EXCLUSIVE (or just WHERE DATE_FIELD BETWEEN :dt AND :dt+1 EXCL)