Oracle's BETWEEN keyword

I came across a new Oracle keyword today, BETWEEN.

At first, I thought it was pretty cool. I would be able to simplify the majority of the range checks that I perform. Before I really started using it though, I decided to look at what it actually did. Ouch!

Google, my ever present documentation source, told me that between doesn't work the way I thought it would. It's inclusive of both ends of the range. Who would want that? You never want a range that is inclusive of both ends! Otherwise, elements in contiguous ranges have indeterminate ownership!

Let's try an example:

We want all rows with a date on the row that has a date of today.

  select id from test_table where my_date between 
    trunc(sysdate) and trunc(sysdate) + 1;

Great you would think, almost too easy!

You would be correct too, it was too easy. It doesn't work. Since it is inclusive of the end of range, you get all values that have a my_date of 12:00:00 tomorrow. What you really want is:

  select id from test_table where 
      (my_date >= trunc(sysdate)) and 
      (my_date < trunc(sysdate) + 1);

When using new keywords and abstractions, you should always know what they are doing.