CODEWITHSUNDEEP
javasqloracle-database
the other me
the other me

Reputation: 23

Insert date works in SQL Developer but not through Java

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?

Upvotes: 0

Views: 398

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 3

John Matthew Ian Davis
John Matthew Ian Davis

Reputation: 44

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.

Upvotes: 2

Related Questions