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 don't get any results from above query. I think I have to use "like" and "%". But I don't know where to use them. Please throw some lights on this.

Stator

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

Barbur

I cant pass the time only date. – Stator

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

Advantageous

Thanks for your swift reply. But I am getting the From and To date from the action file in this format "17-JAN-10". So itried like this: SELECT * FROM uson_assetaccess WHERE accessdate BETWEEN TO_DATE('17-FEB-10','dd-MMM-yy') AND TO_DATE('17-FEB-10', 'dd-MMM-yy'); But i got an error: ORA-01821: date format not recognized – Stator

You just have to use different format specifiers, replace the "MMM" with "MON", see my edit. – Advantageous

Thanks again for immediate response. But I have used that query But now Date format error is gone. now i got no data found error even data are there in the table. I think in table date is there with time but we are sending with date only. Is it because of that reason? Please clarify – Stator

It depends where in the day you're concerned with. I think that just specifying the date will have it only go from/to midnight (the beginning) of that day. If you want it to include anything on Jan 17, you need to add the time, so make the second part of the query: AND TO_DATE('17-JAN-10 23:59:59','DD-MON-YY HH:MI:SS') – Advantageous

I cant get the time when filtering date. – Stator

Still I am getting problem.. my query is: SELECT * FROM uson_assetaccess WHERE accessDate BETWEEN TO_DATE('17-FEB-10','DD-MON-YY') AND TO_DATE('17-FEB-10','DD-MON-YY'); In oracle database field is "17-FEB-10 04.25.32.000000 PM" as Time _Stamp. So it is not matching with that. how to resolve this? – Stator

Answer 3

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.

Unpracticed

Answer 4

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

Envelope

Answer 5

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

Waldman

If a table is big and date column is indexed, you should avoid expressions, use start_date between to_date('01/15/2010','mm/dd/yyyy') and to_date('01/17/2010','mm/dd/yyyy') + 1 instead. – Bewilderment