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!