Re: ?:TO_DATE: ORA-01843: not a valid month

pavelf01_at_mcrcr6.med.nyu.edu wrote:
>
> I'm developing a web app that includes macros (1 each for text, numeric, and
> date form entry fields) that "build" the "set" and "where" clauses for the
> SQL update syntax by concatenating all the updated fields appropriately
> --including single quotes as needed for text fields; Oracle "to_date"
> conversion of date fields. This works well for text and numeric fields, but
> I keep getting the following Oracle error when I update a date:
> Error: SQL request failed: ORA-01843: not a valid month
>
> Following is the app log file entry that shows the dumped value of
> the SET clause that is concatenated which includes 1 date field that I
> manipulate with the to_date function in order to have Oracle accept it
> as a valid date field:
> ********************************************************************************
> DUMP: value of set_clause is medical.intake_dt = to_date('09/15/1993',
> 'MM/DD/YYYY'),medical.pregnancy_nu = 3,medical.birth_control_fl = 'Y',
> medical.birth_control_type = 'Orthonovum'
> ********************************************************************************
> I used the same statement format interactively in SQLPLUS to update a record
> and it worked just fine:
> ********************************************************************************
> SQL> update medical
> 2 set medical.intake_dt = to_date('08/12/1989','MM/DD/YYYY')
> 3 where pat_id = 1;
>
> 1 row updated.
>
> SQL> select pat_id, intake_dt from medical;
>
> PAT_ID INTAKE_DT
> ---------- ---------
> 1 12-AUG-89
> 3
> 10
> 4
> 11 03-SEP-97
> 11 15-SEP-88
>
> 6 rows selected.
> ********************************************************************************
>
> If you happen to notice anything blatantly wrong in the syntax of my set
> clause, or have any suggestions as to what else I can try to get Oracle to
> accept my date fields, please let me know. Thanks for your help.
>
> +----------------------------------------------------------------------------+
> |Frieda Pavel, Systems Mgr, Research Computing Resource, NYU Medical Center |
> |E-Mail: PAVELF01_at_MCRCR.MED.NYU.EDU
> +----------------------------------------------------------------------------+

Does not look like the problem is in the clause you included in the posting. What host language are you using and what is in the WHERE clause?

Gary England Received on Tue Sep 23 1997 - 00:00:00 CDT