Oracle Between Date Request

Question

I am using oracle database. I want to execute one query to check 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 am not getting any results from the above query. I think I need to use "like" and "%". But I do not know where to use them. Please throw some light on this.

early.

Answer 1

Judging by your result, it looks like you defined START_DATE as a timestamp. If it were a regular date, Oracle could handle the implicit conversion. But since you do not need to explicitly specify these strings as 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 get only one line. This is because START_DATE has a time element. If we do not specify a time component, Oracle will use it by midnight by default. This is good for the side on the BETWEEN side, but not for the 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> 

change

If you cannot pass the time component, there are several options. One of them is to modify 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 can affect performance because it will disqualify any b-tree index on START_DATE. Instead, you will need to create a functional index.

Alternatively, you can add a time element to the date of 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 using BETWEEN by checking date borders as follows:

 where start_date >= to_date('15-JAN-10') and start_date < to_date('18-JAN-10') 

Answer 2

You need to convert them to actual dates instead of strings, try the following:

 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 work with the specified format:

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

Answer 3

As APC correctly pointed out, your start_date column looks like TIMESTAMP, but it could be TIMESTAMP with a local TIMEZONE or TIMESTAMP with a data type of TIMEZONE. This can affect any queries you made to the data if your database server was in a different time zone for yourself. However, let’s keep it simple and suppose you are in the same time zone as your server. First, to give you confidence, make sure start_date is a TIMESTAMP data type.

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

eg

DESCRIBE mytable

Must inform:

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

If it is listed as Type = TIMESTAMP, you can query for date ranges with a simple TO_TIMESTAMP date conversion that does not require an argument (or image).

We use TO_TIMESTAMP so that the optimizer takes into account any index in the START_DATE column. APC's response also noted that a function-based index can be created in this column, and this will affect the SQL predicate, but we cannot comment on this in this query. If you want to know how to determine which indexes were applied to the table, send another question and we can answer it separately.

So, suppose there is an index in start_date, which is a TIMESTAMP data type, 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, but not quite 1, so the 17-JAN-10 conversion will be as close to midnight as possible on this day, so you are requesting a return of both lines.

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

Hope this helps.

Dazzer

Answer 4

Date Between Requests

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

Answer 5

The following query can also 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'))