java.sql.SQLException: ORA-01843: not a valid month

You think it is easy to insert a date into a database? Then you never tried it on an Oracle database! Today I spent some time on the „java.sql.SQLException: ORA-01843: not a valid month“ – exception. It was caused by the query

Query: UPDATE myTable SET dataSent=?, dataSentDate=? WHERE email=? Parameters: [Y, 2010-05-17 11:49:50, name@mydomain.com]>

… but the date looks good, or? Some documentation showing this notation. But Oracle (and the web) says it was caused by a date specified an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON. Action: Enter a valid month value in the correct format. I tried to change my SimpleDateFormatter…

new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
new SimpleDateFormat("yyyy-MMMMM-dd hh:mm:ss").format(new Date());
new SimpleDateFormat("yyyy/MM/dd hh:mm:ss").format(new Date());

.. but nothing woked. Finally I found the solution: you have to tell Oracle the formatting of the date! So changing the insert statement like this…

update("UPDATE member SET dataSent=?, dataSentDate=to_date(?,'DD/MM/YYYY HH24:MI:SS') WHERE email=?", updateParams);

.. and of course change the SimpleDateFormatter to the same brings me to the solution!

Advertisements

2 Antworten zu “java.sql.SQLException: ORA-01843: not a valid month

  1. Well, Oracle isn’t particularly famous for easy handling by developers… On the other hand, to be fair, this looks like a typecast problem you could get in any other environment as well.
    I can’t tell from your examples but I guess you sent the date as a text object to the database? In that case, you paid the prize for Oracle’s powerful globalization features. In Java, you could make use of the oracle.sql.date class to send an instance of it to the database hassle-free.

  2. thanks man its a good tutorial.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s