I thought the BETWEEN boundaries are always closed. Isn’t that pretty clear?

If you’re worried about date part precision in that comparison, you can always TRUNC before you BETWEEN.

> Isn’t that pretty clear?

Nop :) That would be my guess too, but I definitely would not be certain about it, and the dozens of people that are going to read the code won't be either, some of them might make the wrong assumption, and bad things happen. I much prefer to be explicit with `>= <=` too

In the real-world world between almost always implies open boundary. Ask a hundred people to draw a point between two lines. I think none will draw a point on the line. When I enter the programming world, I carry over real-world concepts with me, and this where BETWEEN being closed gets confusing to me. Since I'm not a special snowflake, there must be other people like me who could be confused. Hence why I prefer not to use BETWEEN.

Oh I was thinking the opposite. I work between Monday and Friday, that’s a closed boundary. I have between 5 and 10 oranges, another closed boundary.

Maybe it's a temporal vs spatial vs cardinal issue. I'm sitting "between or in between" two people ( open boundary or exclusive or non-inclusive ). Seems spatial is always open boundary. Park your car between those two trucks. I walked between two clowns.

Ah good explanation. You’re right.

On Postgres, yes.

I wouldn't count on every DBMS correctly implement the standard. Specifically, I would expect Oracle to have some crazy concept of "closed" on specific contexts that don't align with anybody's.

Anyway, in my opinion it shouldn't be used because the boundaries are both closed, and that's an incredibly bad choice. Even when it doesn't matter you have to go and confirm it doesn't matter.