Reputation: 13
I have a date column in a table which is varchar2(10). I am fetching some data from the table using select query. It goes on like:
Select
.........................
from
.........
where
................
and (to_date(masc_date,'dd-mm-yyyy') between
to_date('10-05-2014','dd-mm-yyyy') and to_date('11-05-2014','dd-mm-yyyy'))
The masc_date is the date column which is varchar2(10) and the date values in this column are in the format 'dd-mm-yyyy'. When I execute the above query, I get ORA-08143: not a valid month.
However when I modify my above query as:
Select
.........................
from
.........
where
................
and (to_date(masc_date,'dd-mm-yyyy') between
(select to_date('10-05-2014','dd-mm-yyyy') from dual) and
(select to_date('11-05-2014','dd-mm-yyyy') from dual))
The query works fine. I am not able to find the reason for this error since in my first query, the date value in all "to_date" is in the same format and a char value.
Note:The nls language in my database is american and the date format is "dd-mon-rr"
Upvotes: 1
Views: 3106
Reputation: 10551
First thing to do is to check if all your data is indeed in dd-mm-yyyy format. More often than not the data can not be trusted.
declare
cursor c is select masc_date from table;
l_date date;
begin
for r in c loop
begin
l_date := to_date(r.masc_date,'dd-mm-yyyy');
exception
when others then
-- print out date that is not in the form dd-mm-yyyy
dbms_output.put_line(r.masc_date);
end;
end loop;
end;
Upvotes: 1