Oracle SQL Date Ranges

Question

I have the following query:

select * from sa_tran_head WHERE
((TRAN_DATETIME) BETWEEN NVL(:STARTDATE, TRAN_DATETIME) AND NVL(:ENDDATE, TRAN_DATETIME));

If I put STARTDATE = 21-JUN-2016 and ENDDATE = 21-JUN-2016, it doesn't show anything. When I put STARTDATE = 21-JUN-2016 and ENDDATE = 22-JUN-2016, it shows the results of 21-JUN-2016.

Why does it happen?

score 1 · Answer 1 · answered Jun 23 '16 at 09:53

1

My guess? TRAN_DATETIME also stores time value?

Use TRUNC() :

select * from sa_tran_head
WHERE TRUNC(TRAN_DATETIME) BETWEEN NVL(:STARTDATE, TRUNC(TRAN_DATETIME)) 
                               AND NVL(:ENDDATE, TRUNC(TRAN_DATETIME));

answered Jun 23 '16 at 09:53

sagi

40,026
6
59
84

Using `TRUNC( TRAN_DATETIME )` will mean that any index on that column cannot be used. To use an index you would need a function-based index. – MT0 Jun 23 '16 at 10:06
To use an index he can just use his current query with range between yesterday and today or something @MT0 – sagi Jun 23 '16 at 10:09

score 0 · Answer 2 · answered Jun 23 '16 at 09:55

0

Datetime are internally treated as proper timestamp. When you mention date as 21-Jun-2016, it is treated as 21-Jun-2016 00:00:00.000. Hence, it is checking for the values in the 21-Jun-2016 00:00:00.000 and 21-Jun-2016 00:00:00.000 in the first condition, explaining the output you get.

answered Jun 23 '16 at 09:55

Sujeet Sinha

2,417
2
18
27

Oracle does not have a `datetime` data type. It has `DATE` and `TIMESTAMP` and they both have a time component. The OP is talking about `DATE`s and these are not stored with millisecond accuracy. – MT0 Jun 23 '16 at 10:19

score 0 · Answer 3 · answered Jun 23 '16 at 10:01

May be ignore hour, minutes and seconds? Look at example:

create table t(v date)
/
insert into t(v) values(to_date('2016-06-23 23:00:00', 'yyyy-mm-dd hh24:mi:ss'))
/
-- no rows
select * from t where
v BETWEEN Nvl(to_date('2016-06-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), v) 
    AND NVL(to_date('2016-06-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), v);
/
-- exactly one
select * from t where
v BETWEEN Nvl(to_date('2016-06-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), v) 
    AND NVL(to_date('2016-06-24 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), v);
/

score 0 · Answer 4 · edited May 23 '17 at 12:16

Oracle stores dates in a 7- or 8-byte data structure which always includes a time component.

When you have a date TO_DATE( '21-JUN-2016', 'DD-MON-YYYY' ) then Oracle will create the date with the time 00:00:00.

If you do:

WHERE TRAN_DATETIME BETWEEN TO_DATE( '21-JUN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS' )
                        AND TO_DATE( '21-JUN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS' )

Then you will only get results which are on the day you want and have the time component 00:00:00.

Now you can use TRUNC() to set the time component to 00:00:00:

WHERE TRUNC( TRAN_DATETIME ) BETWEEN DATE '2016-06-21' AND DATE '2016-06-21'

However, if there is an index on the TRAN_DATETIME column then this query will not use it (it could, however, use a function-based index on TRUNC( TRAN_DATETIME )).

A solution that can use the index is:

WHERE TRAN_DATETIME >= :start_date 
AND   TRAN_DATE     <  :end_date + INTERVAL '1' DAY

(Assuming that :start_date and :end_date are bind variable passed in with a time component of 00:00:00 - which is what you appear to be doing).

Incorporating the NVL statements:

WHERE ( :start_date IS NULL OR TRAN_DATETIME >= :start_date )
AND   ( :end_date   IS NULL OR TRAN_DATE     <  :end_date + INTERVAL '1' DAY )

Oracle SQL Date Ranges

4 Answers4