Oracle date "Between" Query

Question

I am using oracle database. i want to execute one query to check the data between two dates.

NAME               START_DATE     -------------    -------------  Small Widget       15-JAN-10 04.25.32.000000 PM       Product 1          17-JAN-10 04.31.32.000000 PM      select * from <TABLENAME> where start_date   BETWEEN '15-JAN-10' AND '17-JAN-10' 

But I dont get any results from above query. I think I have to use "like" and "%". But I dont know where to use them. Please throw some lights on this.

thanks in advance.

Answer 1

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') 

Answer 2

You need to convert those to actual dates instead of strings, try this:

SELECT * FROM <TABLENAME> WHERE start_date BETWEEN TO_DATE('2010-01-15','YYYY-MM-DD') AND TO_DATE('2010-01-17', 'YYYY-MM-DD'); 

Edited to deal with format as specified:

SELECT * FROM <TABLENAME> WHERE start_date BETWEEN TO_DATE('15-JAN-10','DD-MON-YY') AND TO_DATE('17-JAN-10','DD-MON-YY');