Re: ORA-01843 - not a valid month

Edwina -

You didn't give a description of your cmn_main, cad_address, or clr_clreport tables, and that's too bad. I think that your problem arises because one of your note_date values is null.

I've made up a small test case that illustrates why I believe this:

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ SQL> create table dummy400 (dummy1 date, dummy2 date)   2 ;

Table created.

SQL> insert into dummy400 values (sysdate, null);

1 row created.

SQL> insert into dummy400 values (null, sysdate);

1 row created.

SQL> select to_date(
  2 to_char(dummy1,'DD/MM/YYYY') || to_char(dummy2,' HH24:MI'),   3 'DD/MM/YYYY HH24:MI'
  4 ) from dummy400;
ERROR:
ORA-01843: not a valid month

no rows selected

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

I'll bet that this is your problem. Other than this, I like your code!

Tom

Edwinah63 wrote:
>
> Hi everyone,
>
> when i run an oracle stored proc in sql*plus, i get "ORA-01843: not a
> valid month". i have checked the query syntax, and the source data
> (the dates and times appear to be in order). this proc sometimes
> returns records, before this error is returned.
>
> the problem appears to be because i am using a date/time component in
> the where statement (i have to be able to do this). if i just use
> dates, it runs, but will not be useable for reporting purposes. i
> take in dates and times as string parameters, and convert them to
> dates after manipulation.
>
> has anyone else had this error while trying to filter on both date and
> time??
>
> the code for the proc is below
>
> i need an urgent fix on this - all thoughts greatly appreciated
>
> Edwina
>
> *****procedure code *****
> ast005_clientrpt_all
> (cur IN OUT pkgassist.rtncur, stdt IN VARCHAR2, sttime IN VARCHAR2,
> enddt IN VARCHAR2, endtime IN VARCHAR2)
> as
>
> st Date;
> en Date;
>
> begin
>
> st := to_date((stdt || ' ' || sttime),'DD/MM/YYYY HH24:MI');
> en := to_date((enddt || ' ' || endtime),'DD/MM/YYYY HH24:MI');
>
> open cur for
> Select CMN_MAIN.CASE_NO, cli_code, pol_code, header, open_date,
> surname, first, cad_address.type, contact, company, street, town,
> country, postcode, phone, fax, telex, note_date, to_char(note_time,
> 'HH24:MI'), notes
> From cmn_main, cad_address, clr_clreport
> Where
> (cmn_main.case_no = cad_address.case_no) and
> (cmn_main.case_no = clr_clreport.case_no) and
> (
> (cancel = 'N') and
> (
> to_date(
> to_char(note_date,'DD/MM/YYYY') || to_char(note_time,' HH24:MI'),
> 'DD/MM/YYYY HH24:MI'
> )
> Between st and en);
>
> **** call from sql*plus ****
> set autoprint on
> variable x refcursor
> exec ast005_clientrpt_all(:x,'21/01/2002','00:00','26/01/2002','23:59')

-- 
==================================================
Tom Gaines
Home:       545 S. 41st Street
            Boulder, CO  80305-5912
            303.499.9821
Cell:       303.912.1241
E-mail:     tanguero_at_pcisys.net