Problem with to_date('date_char') in a where clause
SamBNov 21 2006 — edited Nov 21 2006A little confused with this plsql error.
This code works...
select to_date(value,'dd/mm/yy') from kip_lov where domain='MODULE START1'
If I try to put the "to_date(value,'dd/mm/yy')" expression in the where clause I get an invalid year error,
select to_date(value,'dd/mm/yy') from kip_lov where domain='MODULE START1'
AND to_date(value,'dd/mm/yy') = SYSDATE
"Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0"
The variable "value" is a varchar2 in the format dd/mm/yy.
Note: the following code also works fine,
select to_date(value,'dd/mm/yy') - sysdate from kip_lov where domain='MODULE START1'
I have modified my lov table so that it uses the date type now and it works fine. Just curious as to why the above example does not work.
Sam.