Judging from your output it looks like you have defined START_DATE as a timestamp. If it were a regular date Oracle would be able to handle the implicit conversion. But as it isn’t you need to explicitly cast those strings to be dates.
SQL> alter session set nls_date_format="dd-mon-yyyy hh24:mi:ss"
2 /
Session altered.
SQL>
SQL> select * from t23
2 where start_date between '15-JAN-10' and '17-JAN-10'
3 /
no rows selected
SQL> select * from t23
2 where start_date between to_date('15-JAN-10') and to_date('17-JAN-10')
3 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
SQL>
But we still only get one row. This is because START_DATE has a time element. If we don’t specify the time component Oracle defaults it to midnight. That is fine for the from side of the BETWEEN but not for the until side:
SQL> select * from t23
2 where start_date between to_date('15-JAN-10')
3 and to_date('17-JAN-10 23:59:59')
4 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
Product 1 17-JAN-10 04.31.32.000
SQL>
edit
If you cannot pass in the time component there are a couple of choices. One is to change the WHERE clause to remove the time element from the criteria:
where trunc(start_date) between to_date('15-JAN-10')
and to_date('17-JAN-10')
This might have an impact on performance, because it disqualifies any b-tree index on START_DATE. You would need to build a function-based index instead.
Alternatively you could add the time element to the date in your code:
where start_date between to_date('15-JAN-10')
and to_date('17-JAN-10') + (86399/86400)
Because of these problems many people prefer to avoid the use of between by checking for date boundaries like this:
where start_date >= to_date('15-JAN-10')
and start_date < to_date('18-JAN-10')