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?
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));
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.
datetime data type. It has DATE and TIMESTAMP and they both have a time component. The OP is talking about DATEs and these are not stored with millisecond accuracy.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);
/
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 )
