"Literal does not match the format string" error

Question

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

I am trying to copy some of the column values from customer1_details table to customer2_details table. The columns datatype which I am trying to move is TIMESTAMP(6) for TIME_REGISTERED, DATE_DISCHARGED columns and the datatype 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 more helpful if anyone provides the answer without using INSERT INTO ... SELECT ... statement.

Solution 1

YOu can do it in one statement with a query 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+"' "

Solution 2

This is most likely caused by passing your Date and Timestamp variables as Strings to the insert statement.

When you insert or update Date or Timestamp values, there is a default format in which you can pass those values as strings. What you pass is java's idea of how to convert Dates and Timestamps into strings. These two don't seem to match.

Your best bet is probably to use bind variables, then the framework should take care of that.

An Alternative would be to use Oracle's to_date() function, where you can specify the format string. You would then define a format string which considers java's way of representing dates as strings. However, I am not sure if the java representation depends on the locale. If so, you would have to write you 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 an insert-select which bypasses the java layer entirely.

Solution 3

Read the timestamps as strings with getString();

OR call toString() in 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+"') ";