"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.

Why not do it as one statement: `INSERT INTO ... SELECT ...`? — Barmar, May 04 '13 at 19:30
I think the error means that the date format that Java uses when concatenating to a string doesn't match the syntax that Oracle understands when parsing dates. If you want to do it this way, you need to format the date explicitly in the way Oracle understands it. — Barmar, May 04 '13 at 19:32
My actual query is a bit complex i am gathering data from multiple tables and then inserting that data in the customer2_details table.All other columns do not give any error except these three columns as provided above in my question — Akki, May 04 '13 at 19:33
You should still be able to do it in one statement, by using a join. — Barmar, May 04 '13 at 19:33
If you want some help constructing that join, you'll have to show the actual query. — Barmar, May 04 '13 at 19:34
But i am copying the values from column of one table to another so the date format which i fetch from the columns of first table and store it in the variables must be same as what oracle supports. Do i still need to format it again ? Can u please show how to format it if necessary @Barmar — Akki, May 04 '13 at 19:36
You're not copying it as a string. You're assigning to Java variables declared as `Date` and `Timestamp`. When you then concatenate them with `"VALUES('"+timereg"')"` you get whatever format Java chooses to use, not the Oracle format. Does Java jave a prepared statement feature? If so, that would also solve the problem. — Barmar, May 04 '13 at 19:40

score 2 · Answer 1 · answered May 04 '13 at 19:41

2

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+"' "

answered May 04 '13 at 19:41

Barmar

741,623
53
500
612

I accept the answer is correct. But please read my edit to the question.Is it possible to do without using INSERT INTO ... SELECT ... statement – Akki May 04 '13 at 19:50
Probably. But since I don't know Java, I can't write it without doing more research. If you know Java you should be able to figure it out easily based on the advice in my earlier comments. – Barmar May 04 '13 at 19:55

score 1 · Answer 2 · answered May 04 '13 at 19:42

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.

score 1 · Answer 3 · answered May 07 '13 at 18:36

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+"') ";

"Literal does not match the format string" error

3 Answers3