Hugo Kornelis (5/18/2013)
However, with other data types than integer values, there is indeed amibiguity in the English use of "between". On a numeric (non-integer) scale, when a value is between 11 and 35, I expect the values 11.000 and 35.000 to be included, just as (obviously) 11.001 and 34.999, but not 35.001. Just as the SQL operator BETWEEN is defined for numeric data types.However, with points in time, when people say between now and May 25, I expect May 25, 4:28 PM to be still included. For SQL with datetime and similar data types, the cutoff point would be May 25, midnight - one second later would be outside the range.
That's because "between now and May 25" in English treats May 25 as a day with duration, not as a point in time. If something is to happen between now and 25 May it can happen on 25 May, any time on 25 May. Same with months, years, centuries. But "between X and Y" when X and Y are times (with or without date attached) requires X and Y to be treated as points without duration. There are no things with duration in SQL, the SQL date type corresponds more to a time in (UK) English than to a day - its semantics is simply that it is the restriction of the datetime2 type to values which represent the early boundary of a day in the time-zone for which those values are correct.
It could get quite amusing to try to work how between works in various domains in English: is it direction-free, does it include the endpoints, do the endpoints have extent, does the domain of the endpoints have an order and if so is it partial or total, how many dimensions does the domain have, what are its topological properties). But all (current) SQL types are one dimensional and contain only extent-free values, so there it isn't anything like as complicated as English.
(edit: I would not even calls this a 'bug', like I would call certain other "features", but simply a 'bad choice')
Agreed. This is definitely not a bug. I would call this a non-intuitive implementation choice. (And note that this choice is not made by the Microsoft team; they only followed what has been defined in the ISO/ANSI standard for SQL).
Of course it's not a bug in SQL Server. It may or may not have been a bad choice in the standard or in IBM's original SEQUEL language definition or wherever the choice was originally made (directionality was a bad choice, end-point inclusiveness a good one, in my view), but conforming to the standard is certainly not a bug.
Do you really think that end-point inclusiveness is counter-intuitive?
edit: get quote tags right
Tom