Retrieve data within a date range in Oracle

问题:

I have a table tbldeptdivision as follows:

ID  DEPTID  DIVISIONID  FROMDATE    TODATE      REMARKS 
--- ------- ----------- ----------- ----------- --------
21  21      5           31-AUG-99   01-JAN-80   NULL

I have the query

select *
  from tbldeptdivision
 where deptid = 21
   and trunc(sysdate) between to_date(fromdate,'dd-Mon-yyyy')
                          and to_date(todate,'dd-mon-yyyy');

It returns me no value. Can anybody say why? '31-AUG-99' is actually '31-AUG-1999' and '01-JAN-80' is actually '01-JAN-2080'. What will be the exact query?

回答1:

As your todate is a date your problem stems from the useless conversion of the column's value from a date to a varchar and back to a date:

to_date() converts a VARCHAR to a DATE value. If the value you pass to that function is already a DATE Oracle will first implicitely convert your date to a varchar by applying the default NLS format and will then convert that varchar back to a date, again applying the default NLS format.

In the first (implicit) conversion you are losing the century in your year, which consequently is then wrong when the varchar is converted back to a date

So in your case the following is done due to the call to_date(fromdate,'dd-Mon-yyyy')

• todate contains the (real) date value: 1980-01-30
• the implicit conversion to a varchar makes that '01-JAN-80'
• the conversion from the varchar to a date then assumes the year 80 should be 2080 (again based on the rules for implicit data type conversion).

The general rule is:

Do NOT use to_date() on a DATE (or TIMESTAMP) column

If you need to get rid of the time part in the DATE column use trunc() instead:

where trunc(sysdate) between trunc(fromdate) and trunc(todate)

回答2:

Assume FROMDATE/TODATE datatype is varchar2 then when you do to_date;

select to_date('01-JAN-80','dd-mon-yyyy') from dual;
OutPut: January, 01 0080 00:00:00

So it wont be '01-JAN-2080' but '01-JAN-0080'

Even if FROMDATE/TODATE datatype is date tusing to_date is not a good idea;

create table t(c date);
insert into t select sysdate from dual;
select c, to_date(c,'dd-mon-yyyy') from t;

OutPut:

C                           TO_DATE(C,'DD-MON-YYYY')
August, 25 2015 10:55:36    August, 25 0015 00:00:00

Still the year is 0015 not 2015.

If your columns datatype is date then use trunc to get thedate portiondon't useto_date`.

select * 
from tbldeptdivision
where deptid=21
and trunc(sysdate) between trunc(fromdate) 
and trunc(todate)

回答3:

Using functions on fields in your where clause slows down production. This is the same logic and will run faster.

where fromdate <= trunc(sysdate)
and todate > trunc(sysdate )