Re: ORA-01839: date not valid for month specified

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 15 Mar 1999 19:55:04 -0800
Message-ID: <36EDD569.27EA@oriolecorp.com>

• hpcheong_at_ncs.com.sg wrote:
  >
  > hi all, One funny problem arises while testing in my dev inatance. I tried to
  > retrieve 'CL' records from a table with date as one of the comparsion
  > criteria. It returns an error even though records exists. Then I tried with
  > another record 'BD' with the same sql and it works.
  >
  > This was strange as it happen to a specific record type - 'CL'. Further
  > investigation was done at uat instance and the same sql for 'CL' records was
  > tried out. It retrieves record of the specified date!
  >
  > See below for the actual run at sqlplus :-
  >
  > SQL> select test_no from fams_test where test_ctr='CL' and
  > (to_date(test_dt) = to_date('04/03/1999', 'dd/mm/yyyy'));
  >
  > pls help, thanks!
  >
  > evelyn.
  

Since you are applying the to_date() function to your test_dt column, I presume it is not defined as DATE but as VARCHAR2() or CHAR()? Which means that no validity check was performed by Oracle on insert. Quite obviously, your column contains some wrong data, something like a 30th February or 31st June - just what the message says. By the way, since, once again, you do not specify any date format in to_date(), I understand that the date is supposed to follow your default date format. In that case, do not apply a to_date() function on both sides of the equality, just express the constant as a string in the 'good' (i.e. default) date format. What you have written would prevent Oracle from using the index if the column is indexed.

Regards,

  Stéphane Faroult
  Oriole Corporation

---

 http://www.oriolecorp.com, the site created by Oracle DBAs for Oracle DBAs

---

Received on Mon Mar 15 1999 - 21:55:04 CST