CELKO (9/30/2012)
That's certainly one way to do it but that typically results in an OR in the JOIN or WHERE clause which can cause performance problems. With that thought in mind, I'll close the interval with a very high date such as 9999-12-30. Notice that I don't use the last date available (9999-12-31) in SQL Server so that I can use the classic and easily SARGable ..
I don't think the performance hit is that bad today; I find I usually write "x_ date BETWEEN start_date AND COALESCE (end_date, CURRENT_TIMESTAMP)" since it preserves the truth. I cannot see the future, but I am still alive today 🙂
But more than that, we have DATE data types today, so the old Sybase dialect half-open trick is not needed any more. The sensible, easily read BETWEEN is good now, after all the decades of dialect.
Between won't work properly if times are involved. And, although I agree that the relatively new DATE datatype is a Godsend for some, I always enjoy it when someone decides they really do want "time" to be a component of the column and their code starts returning whacky numbers after they change the DATE columns to DATETIME columns. Using the method I suggested, that whacky numbers won't happen even for such a change.
I agree that "x_ date BETWEEN start_date AND COALESCE (end_date, CURRENT_TIMESTAMP)" does a reasonable job (including an index seek for the start_date) for problems up until "now" but it doesn't handle outlying date problems for future reservations and the like.
--Jeff Moden
Change is inevitable... Change for the better is not.