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

Question

I am getting the following error when inserting data into my oracle database.

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

In database date is as: dd-MMM-yy (06-MAR-12)
I am converting 06-03-2012 to dd-MMM-yy by the following method:

String s="06-03-2012";

String finalexampledt = new SimpleDateFormat("dd-MMM-yy").format(new SimpleDateFormat("dd-MM-yyyy").parse(s));

So i got 06-Mar-12 which is same as the above database date format still i am getting the error. I am inserting as:

in index.jsp

 String todaydate="";

Calendar calendar1 = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
todaydate = dateFormat.format(calendar1.getTime());

<input type="text" name="datename" value="<%=todaydate%>"/>

in servlet(doPost)

String s=request.getParameter("datename");

PreparedStatement ps=con.prepareStatement("insert into tablename(rest_dt, othercolname) values (to_date(?, 'dd-mm-yyyy'), ?)");

ps.setString(1, s);
ps.setString(2, otherstringdata);

int  rs=ps.executeUpdate();

Any idea please

Solution 1

so make

("insert into mytablename (rest_dt) values to_date(?, 'DD-MM-YYYY')");  

Try this

TO_DATE(?, 'DD-MM-YYYY','NLS_DATE_LANGUAGE = American')  

// gets from Oracle docs

Solution 2

Problem is that oracle uses NLS_DATE_LANGUAGE to get the current name of the month. So you should do

select * from nls_session_parameters

and check if you have the correct values. You can also check with the following select which name you get for the month

select  TO_CHAR(TO_DATE('01-03-01', 'DD-MM-YY'), 'MON') from dual

I really don't understand why you insert the variable as a string value. Just use a date type (do the conversion on the client) in java and insert it without converting. If you really want to insert it as a string I would use a conversion to something like dd-MM-yyyy and insert it with TO_DATE(, 'DD-MM-YYYY').

Edit:

Do the conversion of the date on the client and use

ps.setDate(2, <yourDate>);

Solution 3

The datatype of your rest_dt columns is a DATE, so you need to supply one. You can use the TO_DATE function to convert a string to an Oracle DATE, so your insert statement

insert into tablename(rest_dt, othercolname) values (to_date(?, 'dd-mm-yyyy'), ?)

is fine.

Just make sure the string value you bind to your first ?-variable is in the format dd-mm-yyyy. And don't convert or format that value yourself: the TO_DATE function does that part.

There is no need to anything about session settings like nls_date_language here, since you have wisely chosen to use a language agnostic setting for the month with your MM mask (instead of MON).

Regards,
Rob.