The error "Literal does not match the format string"

Question

When I try to execute the code below, it gives me the error java.sql.SQLException: ORA-01861: literal does not match format string .

I am trying to copy some column values ​​from customer1_details table to customer2_details table. The column data type I'm trying to move is TIMESTAMP(6) for TIME_REGISTERED , DATE_DISCHARGED , and the data type for DATE_OF_BIRTH column is DATE

  try { Connection conn=Address.getOracleConnection(); int id = 1; Date dob = null; Timestamp timereg = null,datedischarged = null; Statement stmt=conn.createStatement(); ResultSet res=stmt.executeQuery("SELECT TIME_REGISTERED,DATE_DISCHARGED,DATE_OF_BIRTH from customer1_details WHERE customer_id = '"+id+"' "); if(res.next()) { timereg=res.getTimestamp("TIME_REGISTERED"); datedischarged=res.getTimestamp("DATE_DISCHARGED"); dob=res.getDate("DATE_OF_BIRTH"); } String sql1="INSERT INTO customer2_details(TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID) " + "VALUES('"+timereg+"','"+datedischarged+"','"+dob+"','"+id+"') "; PreparedStatement pst=conn.prepareStatement(sql1); pst.executeUpdate(); pst.close(); conn.close(); } catch(Exception e) { System.out.print(e); } 

It will be useful if someone gives an answer without using INSERT INTO ... SELECT ... statement .

Answer 1

You can do this in one statement with a request like:

 "INSERT INTO customer2_details (TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID) SELECT TIME_REGISTERED,DATE_DISCHARGED,DATE_OF_BIRTH, customer_id from customer1_details WHERE customer_id = '"+id+"' " 

Answer 2

This is most likely due to passing the Date and Timestamp variables as strings to the insert statement.

When you insert or update date or time values, there is a default format in which you can pass these values ​​as strings. What you pass in is the idea of ​​Java on how to convert dates and timestamps to strings. It seems that these two are not consistent.

It is best to probably use bind variables, then the structure should take care of this.

An alternative would be to use the Oracle to_date () function, where you can specify a format string. Then you define a format string that considers java's way of representing dates as strings. However, I'm not sure if the java view is locale dependent. If so, you will have to write your own date_to_string () method, which always returns dates in the same format, or your program may work on some computers, but not on others with a different locale.

And finally, you can do insert-select, which completely bypasses the java layer.

Answer 3

Read timestamps as strings using getString ();

OR calling toString () on your java timestamp object instances.

 String sql1="INSERT INTO customer2_details(TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID) " + "VALUES('"+timereg.toString()+"','"+datedischarged.toString()+"','"+dob.toString()+"','"+id+"') ";