Insert date works in SQL Developer but not through Java

Question

I face a problem whenever I want to insert a date from Java to my Oracle database.

Here is a sample of my Java code:

PreparedStatement prest = myConn.prepareStatement(
    "insert into QuickTicket (issueDate , pssngr_id , trip_number) values(?,?,?)") ;

String s = "2016/12/25" ;

prest.setString(1, s);
prest.setInt(2, passengerID);
prest.setInt(3, tripID);

prest.executeUpdate();

Exception:

java.sql.SQLDataException: ORA-01861: literal does not match format string

However, when I put the same statement in SQL Developer:

insert into QuickTicket (issueDate , pssngr_id , trip_number) values
( '2016/12/25' , 1234567897 , 4 ) ;

I get no errors -- > 1 row inserted.

Any idea why?

dear down-voters : you're always welcome to down-vote but please state a reason why so that i can improve my question. — the other me
– the other me, Commented Dec 25, 2016 at 2:43
try replacing the 1st line with this PreparedStatement prest = myConn.prepareStatement("insert into QuickTicket (issueDate , pssngr_id , trip_number) values(?,?,?);") ; — SteelToe
– SteelToe, Commented Dec 25, 2016 at 2:49
@the other me - did you try replacing the 1st line with my code that I presented in the above comment? — SteelToe
– SteelToe, Commented Dec 25, 2016 at 3:17
@MarrieteCowby12 - adding a semicolon will just change the error or ora-00911, as it's an invalid character in a single SQL statement. — Alex Poole
– Alex Poole, Commented Dec 25, 2016 at 8:53

Alex Poole · Accepted Answer · 2016-12-25 09:14:50Z

Your SQL Developer session and Java environment have different NLS settings; one expects the date in YYYY/MM/DD format, the other in something else. You could force the environments to match, but you won't always be able to control that, and it's better not to rely on NLS settings anyway.

You need to either specify the format as part of the insert statement using to_date():

PreparedStatement prest = myConn.prepareStatement(
    "insert into QuickTicket (issueDate , pssngr_id , trip_number) values(to_date(?, 'YYYY/MM/DD'),?,?)") ;

String s = "2016/12/25" ;

prest.setString(1, s);
...

or preferably use a Date variable, and set it with setDate() rather than setString(), changing the format of your String to what valueOf() expects, as JohnMatthewIanDavis pointed out:

PreparedStatement prest = myConn.prepareStatement(
    "insert into QuickTicket (issueDate , pssngr_id , trip_number) values(?,?,?)") ;

String s = "2016-12-25" ;

prest.setDate(1, java.sql.Date.valueOf(s));
...

If you're actually trying to insert the current date you can get that from Java:

prest.setDate(1, new java.sql.Date(System.currentTimeMillis()));

or more simply from within Oracle, using sysdate or current_date (for the server time and the client time, respectively; in this case probably server?):

PreparedStatement prest = myConn.prepareStatement(
    "insert into QuickTicket (issueDate , pssngr_id , trip_number) values(trunc(sysdate),?,?)") ;

prest.setInt(1, passengerID);
prest.setInt(2, tripID);

The trunc() sets the time of the inserted value to midnight. If you want to keep the time, just use plain sysdate.

John Matthew Ian Davis · Accepted Answer · 2016-12-25 04:11:09Z

2

I am guessing issueDate is a Date type in your database, which means you want to bind a Date and not a String

prest.setDate(1, java.sql.Date.valueOf(s));

instead of

prest.setString(1, s);

it should work.

answered Dec 25, 2016 at 4:11

John Matthew Ian Davis

442 bronze badges

3 Comments

John Matthew Ian Davis Over a year ago

Actually you have to change the date "2016/12/25" to "2016-12-25", well.

John Matthew Ian Davis Over a year ago

docs.oracle.com/javase/8/docs/api/java/sql/…

the other me Over a year ago

You nailed it. I thought i would just pass a string and the database will handle it. A huge pile of thanks.

Collectives™ on Stack Overflow

Insert date works in SQL Developer but not through Java

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related