ORA - 01843 Error: Not a valid month

[devnaren]

Hi All,

We are using Crystal Reports XI to create reports and executing those reports from the Business Objects XI R2 and using Oracle 9i database.
I created a report, in that i am using an object SubmittedDate which is coming from universe. In the database the field datatype is varchar2(255). So I created object in universe as to_date({field}, 'mm/dd/yyyy').

On the report side, i created two parameters as StartDate and EndDate which is set to Date format.

Before 29th of Feb this report was giving the result as what we need. when i started executing this report on 29th of Feb, its not giving me any result.

In my Record Selection formula it is written as

{SubmittedDate} >= {StartDate} and
{SubmittedDate} <= {EndDate}.

its giving me an error as

ORA-01843: Not a Valid Month.



when i check it in google about this issue, its says like NLS_DATE_FORMAT.

I don't understand what would be the issue.

Can anyone help me out in this issue.

 

[SantaMufasa]

Devnaren,

This is a good question. The answer is that whatever information you are entering for your date, the value for the Month entry is bogus as far as Oracle is concerned.

So, if you are using the date-format mask, 'mm/dd/yyyy', then the first two characters must be in the set {01,02,03,04,05,06,07,08,09,10,11,12}. If, however, you are using Oracle's default date format, "DD-MON-YY", then characters 4-6 must be in the set "JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC".

To confirm the National Language Set (NLS) settings that affect you, then issue this query:

select *
from nls_session parameters;

Notice that one of the entries is "NLS_DATE_FORMAT".

Let us know if this provides you information that contributes to the resolution of your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[devnaren]

In the database the Completion Date values stored as
10/22/2007 and its datatype is varchar2(255). so for that i created a object in the universe and converting this completion date field as
to_date(completiondate, 'mm/dd/yyyy') and type as Date and saved this object as CompletionDate_Object.

On the report side, i created a two parameters as Start Date,
End Date.

On the record selection formula i'm writing the formula as
CompletionDate_Object >= StartDate and
CompletionDate_Object <= EndDate.

So its giving me an error as Failed to retrieve data from the database and its giving as Not a Valid month.

 

[SantaMufasa]

Devnaren,

Following will help you isolate which rows are causing problems for you. (Replace "table_name" with the correct table name for your environment.) BTW, I ran the following from a plain SQL*Plus prompt. That would also work best for you, too, to run this code. If you do not have access to SQL*Plus, then remove the SQL*Plus "SET" statements or do whatever else you need to do to run the code in your particular environment. Notice, below that there are two rows with invalid month numbers, and my PL/SQL block identifies those two rows and the code ignores the valid rows.

SQL> select * from table_name;

COMPLETION_DATE
------------------------------
03/03/2008
10/30/2008
13/05/2008
25/10/2008
01/18/2006

set serveroutput on format wrap
declare
    hold_date date;
begin
    dbms_output.enable(1000000);
    for x in (select rowid rn,Completion_date from table_name) loop
       begin
           hold_date := to_date(x.completion_date,'mm/dd/yyyy');
       exception
           when others then
               dbms_output.put_line(x.rn||': '||x.completion_date||': '||sqlerrm);
       end;
    end loop;
end;
/

AAAFUvAAEAAAC4yAAC: 13/05/2008: ORA-01843: not a valid month
AAAFUvAAEAAAC4yAAD: 25/10/2008: ORA-01843: not a valid month

Let us know your findings.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimbopalmer]

I spent a LOT of time looking at my data once with this error before finding that 0ct was not the same as Oct, someone had a zero in their field. I hope you find the similar problem you are having.

I tried to remain child-like, all I acheived was childish.

 

[ChrisHunt]

In the database the Completion Date [...] datatype is varchar2(255).

That doesn't sound like a good design decision - why use a varchar field to store a date?

If somebody has somehow input an invalid date into that field, the first time you'll know about it is when you try to TO_DATE it into something useful. My guess is that this is what's happening here.

Try something like this to identify the dodgy data (using the underlying varchar field instead of the TO_DATEed version):

SELECT *
FROM   table_name
WHERE  SUBSTR(submitted_date,4,2) NOT IN
      ('01','02','03','04','05','06','07','08','09','10','11','12')

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Devnaren,

Chris's SQL code is excellent to isolate values that will throw "not a valid month". You could expand Chris's code to also check for other data errors that could cause a DATE exception. (And, the code I posted, above, will identify any error on any row that would cause a DATE exception.)

Let us know how your investigation progresses.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kskid]

For Crystal XI, try entering the date parameters in yyyy-mm-dd format.