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

From: <pavelf01_at_mcrcr6.med.nyu.edu>
Date: 1997/09/11
Message-ID: <TtH0h3zP5rA6@mcrcr6>#1/1

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

+----------------------------------------------------------------------------+

Received on Thu Sep 11 1997 - 00:00:00 CDT