CODEWITHSUNDEEP
oracle-database
Mayank
Mayank

Reputation: 13

ORA-08143(not a valid month)

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

Answers (1)

Rene
Rene

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

Related Questions