var foo date := trunc(sysdate);\nselect *\n from tab\n where start_date <= foo\n and (end_date is null or end_date <= foo);
The above might have a better optimizer profile than the query you presented. The accuracy of this query assumes start_date and end_date are trunc()ed before insertion into the table.
This is the same logically as
where start_date <= foo and (nvl(end_date, foo) <= foo)
, but probably more efficient since 'is null' is probably faster than a date compare. It's late and I'm just noodling, though. :-)If you start using things like +1, you have to be very careful to ensure you're actually getting the results you're expecting. Test all of the boundary conditions thoroughly.