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?
Assume
FROMDATE/TODATE
datatype isvarchar2
then when you doto_date
;So it wont be
'01-JAN-2080'
but'01-JAN-0080'
Even if
FROMDATE/TODATE
datatype isdate
tusingto_date
is not a good idea;OutPut:
Still the year is
0015
not2015
.If your columns datatype is date then use
trunc to get the
date portiondon't use
to_date`.Using functions on fields in your where clause slows down production. This is the same logic and will run faster.
As your
todate
is adate
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 aVARCHAR
to aDATE
value. If the value you pass to that function is already aDATE
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'01-JAN-80'
The general rule is:
Do NOT use to_date() on a
DATE
(orTIMESTAMP
) columnIf you need to get rid of the time part in the
DATE
column usetrunc()
instead: