[sql] Oracle date "Between" Query

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.

Following query also can be used:

select * 
  from t23
 where trunc(start_date) between trunc(to_date('01/15/2010','mm/dd/yyyy')) and trunc(to_date('01/17/2010','mm/dd/yyyy'))

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

As APC rightly pointed out, your start_date column appears to be a TIMESTAMP but it could be a TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE datatype too. These could well influence any queries you were doing on the data if your database server was in a different timezone to yourself. However, let's keep this simple and assume you are in the same timezone as your server. First, to give you the confidence, check that the start_date is a TIMESTAMP data type.

Use the SQLPlus DESCRIBE command (or the equivalent in your IDE) to verify this column is a TIMESTAMP data type.

eg

DESCRIBE mytable

Should report :

Name        Null? Type
----------- ----- ------------
NAME              VARHAR2(20) 
START_DATE        TIMESTAMP

If it is reported as a Type = TIMESTAMP then you can query your date ranges with simplest TO_TIMESTAMP date conversion, one which requires no argument (or picture).

We use TO_TIMESTAMP to ensure that any index on the START_DATE column is considered by the optimizer. APC's answer also noted that a function based index could have been created on this column and that would influence the SQL predicate but we cannot comment on that in this query. If you want to know how to find out what indexes have been applied to table, post another question and we can answer that separately.

So, assuming there is an index on start_date, which is a TIMESTAMP datatype and you want the optimizer to consider it, your SQL would be :

select * from mytable where start_date between to_timestamp('15-JAN-10') AND to_timestamp('17-JAN-10')+.9999999

+.999999999 is very close to but isn't quite 1 so the conversion of 17-JAN-10 will be as close to midnight on that day as possible, therefore you query returns both rows.

The database will see the BETWEEN as from 15-JAN-10 00:00:00:0000000 to 17-JAN-10 23:59:59:99999 and will therefore include all dates from 15th,16th and 17th Jan 2010 whatever the time component of the timestamp.

Hope that helps.

Dazzer

Date Between Query

SELECT *
    FROM emp
    WHERE HIREDATE between to_date (to_char(sysdate, 'yyyy') ||'/09/01', 'yyyy/mm/dd')
    AND to_date (to_char(sysdate, 'yyyy') + 1|| '/08/31', 'yyyy/mm/dd');