ORA-01843 NOT A VALID MONTH ERROR

[SalShaikh]

hi all sql/oracle gurus and experts,
I need assistance with the following problem
I have a table (structure follows)
Name Null? Type
------------------------------- -------- ----
SAMPLE_ID NOT NULL NUMBER(10)
NAME VARCHAR2(40)
TEXT_VALUE VARCHAR2(255)
HIDDEN CHAR(1)

I need to convert the text_value to a date so I can use it in date range where clauses
when i do a simple select with a to_date(text_value, 'MON-DD-YYYY) it works fine
but when i try it in where clause as below I get a ORA-01843 not a valid month error... any help would be appreciated.


SELECT NAME,
TEXT_VALUE,
TO_DATE(TEXT_VALUE, 'MON DD YYYY')
FROM NAIS_SAMPLE_ATTRIBUTES
WHERE SAMPLE_ID = 200000288
AND NAME = 'COLLECTION DATE'
AND TO_DATE(TEXT_VALUE, 'MON DD YYYY') = TO_DATE('JUN 24 2003','MON DD YYYY')
/

 

[carp]

Is it possible that some of your text_value data is honked up?
For instance, if your data looks like 'JLY 23 2002' you would get that kind of error.

You might try something like

set serveroutput on size 1000000
BEGIN
FOR i IN (SELECT text_value FROM NAIS_SAMPLE_ATTRIBUTES) LOOP
dbms_output.put_line(i.sample_id,
i.text_value,
to_date(i.text_value,'MON DD YYYY'));
END;
/

This should blow up when it hits data that doesn't match your template, but SHOULD print out the data itself.

 

[carp]

Boy I hate it when I forget to END LOOP! Try this instead of the previous:

set serveroutput on size 1000000
BEGIN
FOR i IN (SELECT text_value FROM NAIS_SAMPLE_ATTRIBUTES) LOOP
dbms_output.put_line(i.sample_id,
i.text_value,
to_date(i.text_value,'MON DD YYYY'));
END LOOP;
END;
/
Although now that I think about it some more, here's an even better approach:

set serveroutput on size 1000000
DECLARE
l_date DATE;
BEGIN
FOR i IN (SELECT * FROM NAIS_SAMPLE_ATTRIBUTES) LOOP
BEGIN
l_date := to_date(i.text_value,'MON DD YYYY');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ID# '||i.sample_id||
'Name: '||i.name||
'BAD DATE: '||i.text_value);
END;
END LOOP;
END;
/

This will flag only the bad dates and will process the entire table.

 

[ddrillich]

Good Day Carp,

I tried to use some of your code with my data –

set serveroutput on size 1000000
BEGIN
FOR i IN (SELECT file_datetime FROM maint_data) LOOP
dbms_output.put_line(i.file_datetime);
END;

I get –

ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
Loop

Any ideas?

Thanks,
Dan

 

[carp]

See preceding post.

 

[SalShaikh]

THANKS FOR YOUR RESPONSE...

but i figured it out...

in oracle sql process from the bottom up
and all i did was move the function that was causing the error above the clause that narrowed down the number of rows returned to be only the proper dates.

 

[sem]

You probably will be distressed with it, but Oracle doesn't guarantee the order of processing. Even

select * from

(SELECT NAME,
TEXT_VALUE,
TO_DATE(TEXT_VALUE, 'MON DD YYYY')
FROM NAIS_SAMPLE_ATTRIBUTES
WHERE SAMPLE_ID = 200000288
AND NAME = 'COLLECTION DATE')

where TO_DATE(TEXT_VALUE, 'MON DD YYYY') = TO_DATE('JUN 24 2003','MON DD YYYY')

may fail, because of pushing outer condition into subquery.
One day your code may stop to work.

Regards, Dima

 

[dsanchez2]

It is true that in Oracle the order in which a statement is processed is not pre-determined and it may change. The optimiser may execute the statement in the order that will result in the best performance. The plan selected by the optimiser may change as the data changes, or if you add an index to the table, etc.

The main lesson learned from this is to always store dates in columns of type DATE (or TIMESTAMP). This will stop bad data getting into the database on the first place.

 

[andys288]

I have a similar problem when running Cognos Impromptu and oracle 8i database. I have "tweaked" the above script to fit by data structure and found some erroneous values which I have updated. BUT I am still getting the invalid month problem.

Anybody got any other ideas?

 

[sem]

Other idea is that you updated NOT ALL values. Can you loop through this table:

declare
mRowid rowid;
mDate date;
begin
for f in (select rowid, <field> from <table>) loop
mRowid := f.rowid;
mDate := to_date(f.<field>);
end loop;
exception
when others then
dbms_output.put_line('Rowid you need is '||mRowid);
end;


Regards, Dima

 

[andys288]

declare
mRowid rowid;
mDate date;
begin
for f in (select rowid, supplier_ref from poheadm) loop
mRowid := f.rowid;
mDate := to_date(f.supplier_ref);
end loop;
exception
when others then
dbms_output.put_line('Rowid you need is '||mRowid);
end;



I got the following output:
Rowid you need is AAAB4RAAHAAABEhAAA

Hope this means something to you, as I'm cluesless!

 

[sem]

You may query your table by that rowid and fiund incorrect value you missed.

Regards, Dima

 

[SantaMufasa]

Andys288,

I've taken the liberty to suggest a couple of slight
alterations to Dima's fine script. The modifications are:
1) Placing the EXCEPTION code inside a subordinate block for the offending-date code. Because this block is entirely inside the loop, this causes the exception to fire for as many times as it encounters a bad date in the &quot;poheadm&quot; table. (The way it was, you'd need to run the code for as many times as there were bad dates. This way you get them all in one run.)
2) I modified the &quot;dbms_output.put_line&quot; to generate a line of SQL code to update each bad date in &quot;poheadm&quot;. By spooling the output to &quot;FixDates.sql&quot;, you would need to manually edit each of the bad dates appearing in that file to a valid date, but at least when you run that script (after your manual edits), you have all the dates cleaned up in &quot;poheadm&quot;.

Here is the revised code (which you can copy and paste into a SQL*Plus session:

spool FixDates.sql
declare
mRowid rowid;
mDate date;
begin
for f in (select rowid, supplier_ref from poheadm) loop
mRowid := f.rowid;
begin
mDate := to_date(f.supplier_ref);
exception
when others then
dbms_output.put_line
('update poheadm set supplier_ref = '||
'''f.supplier_ref'' where rowid = ''f.rowid'';');
end;
end loop;
end;
/
spool off

Let me know how this works for you.

Dave

 

[sem]

Dave:

I also thought about exception handler, but

1. The reason for writing a script was to show existance of such dates.
2. To get output, it should be enabled at least(disabled in sql*plus by default).
3. If the number of rows is huge, dbm_output may fail due to buffer size restrictions.
4. ... the last but not the least: I'm too lazy to provide complete solutions like yours .


Though, I suppose it should be

'update poheadm set supplier_ref = '''||f.supplier_ref||
''' where rowid = '''||f.rowid||''';'

to edit supplier_ref manually.

andys288:

Are you sure that supplier_ref should contain dates? I suppose that _ref may stand for reference and contain some number/abbreviation.


Regards, Dima

 

[andys288]

I used the amended script of dima's as the first one just scrolled and scrolled.

Routine ran and did not find any dodgy data, but when I try my to-date command I still get an invalid month error.

The supplier_ref field was originally a 20 character text field on a Sage line 500 database (formerly known as Tetra). We have applied some input format commands to this now, so that it only accepts dates in the format 31/12/99 etc. But because its still a character column in Oracle, cognos does not recognise it as a date, so I have to use the to_date (or make_datetime in cognos). but this is giving me the errors.

Confused?

 

[SantaMufasa]

Dima, Your correction to my code is right on. (I should have done a SQL*Plus &quot;set dwimnwit on&quot;; that's the handy &quot;Do What I Meant; Not What I Typed&quot; option.<smile>) My code would print out the literal 'f.supplier_ref', not its value, which is what I wanted.

Also, to deal with issues #2 and #3 in Dima's most recent reply, add these lines prior to script execution:

set serveroutput on
exec dbms_output.enable (1000000)

The first command allows SQL*Plus to write to the screen from PL/SQL's dbms_output.put_line package. The second command increases the dbms_output buffer from its miniscule 2KB to about 1MB.

Now, Andy, when you say, &quot;...Routine ran and did not find any dodgy data, but when I try my to-date command I still get an invalid month error,&quot; I'm puzzled. Unless every entry in &quot;supplier_ref&quot; has valid entries that appear using your system's default date format (probably &quot;DD-MON-YY&quot, then you should be seeing plenty of &quot;dodgy&quot; entries.

Could you please post perhaps a dozen of the existing erroneous values that you are encountering for &quot;supplier_ref&quot;?

Thanks,

Dave

 

[andys288]

I've worked out what my problem is! Oracle (as you all will know and I should have remembered!) stores dates in the format 13-AUG-03, but my Supplier_ref column is storing the data 13/08/03, therefore causing the &quot;invalid month&quot; error. Now that I have solved that mystery, my next problem is that I have about 12,000 records all with the wrong date format. Rather than manually change each record into the new format I was thinking I should be able to extract the data into an excel file, and get excel to change the date format before re-inserting the data into the table.

Their is a unique key in the table which is order_no, so It should be possible to write a script to do this, but.... to save me spending hours trying to figure it out can one of you guys help?

Cheers
Andy

 

[sem]

Oracle, as we all (or at least some of us) know DOESN'T store dates in this format. It uses 8-byte binary representation. Though, under some circumstances, it uses this format mask as a default one to convert DATE to CHAR and v.v. Are all the records in the same &quot;wrong&quot; format (which one, BTW?)? I also suppose that in any case using excell is quite unreasonable.

Regards, Dima

 

[andys288]

The format they currently are in is &quot;13/08/03&quot;. I have already been able to extract the information incluidng order_no into excel using microsoft query, and now have manipulated them into the format &quot;13-AUG-03&quot;.

All I want to do now is put them back into oracle.

 

[sem]

Without MS you may:
1. To obtain ALL supplier_refs in &quot;default&quot; format:

update poheadm set supplier_ref = to_date(supplier_ref,'dd/mm/rr')

2. To query it without changes:

select * from poheadm where supplier_ref= to_char(<some date>,'dd/mm/rr')

or

select * from poheadm where to_date(supplier_ref,'dd/mm/rr')=<some date>



Regards, Dima