java.lang.IllegalArgumentException: Invalid format: "dd-MM-yyyy HH:mm:ss"

Question

I'm tryng to insert a date into a db Oracle column. The dataType is Date. I receive from web api an org.threeten.bp.OffsetDateTime and convert it in org.joda.time.DateTime. A query reads all paramaters, and one of these contains this value to_date('"+date.parse("dd-MM-yyyy HH:mm:ss")+"','DD-MM-YYYY HH24:MI:SS').

I'm debugging my problem and when I reach this value of the query to_date('"+date.parse("dd-MM-yyyy HH:mm:ss")+"','DD-MM-YYYY HH24:MI:SS'), I get the messagge:"java.lang.IllegalArgumentException: Invalid format: "dd-MM-yyyy HH:mm:ss"".

because this is OffsetDateTime format 2019-02-20T09:45:35.388209800Z, I need to change the date in this format "dd-MM-yyyy HH:mm:ss".

    String date = null;
    String[] date1=t.getUtcDate().toLocalDate().toString().split("-");

            for(i=date1.length-1; i>=0; i--) 
            {
                if(date==null) {
                date=date1[i];

                }
                else
                {
                    date=date+"-"+date1[i];

                }
            }
            System.out.println(date);


            String time=t.getUtcDate().toLocalTime().toString();

            String time1=time.substring(0, time.indexOf("."));
            System.out.println(time1);
            String dateTime=date+" "+time1;
            System.out.println(dateTime);
            DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-MM-yyyy HH:mm:ss");

            DateTime dt = formatter.parseDateTime(dateTime);


 String query = "INSERT INTO TABLENAME (DATE)"
                                      + " values (to_date('"+date.parse("dd-MM-yyyy HH:mm:ss")+"','DD-MM-YYYY HH24:MI:SS'))";

Without this part of code to_date('"+date.parse("dd-MM-yyyy HH:mm:ss")+"','DD-MM-YYYY HH24:MI:SS'), my query works well.

If the date starts with a year, why are you parsing starting with the day? — OneCricketeer
Something is not right here. The pattern you should be following is taking a Java Date or better yet a LocalDate and then using the JDBC API to directly bind that value to your insert statement. The heavy lifting your are doing has already been done elsewhere, and you should not reinvent the wheel. — Tim Biegeleisen
You can always do an ALTER SESSION SET NLS_DATE_FORMAT execute onto the DB for the session to set the format of the date just while you are inserting values. — Nexevis
On a side note (because IMO the comments above address the more basic problem): date.parse("dd-MM-yyyy HH:mm:ss") won't work as expected since date is a String which doesn't have a parse() method. And if you meant formatter.parse(...) you'd do it the wrong way and with the wrong parameter: parse() would require an actual date string and not a patter but you'd want to use format() to produce a string from a Date. I'll reiterate though: that's just a side note and you should do what Tim suggests and pass the Date object directly to the JDBC api. — Thomas

Andreas Andreas · Accepted Answer · 2019-05-06T15:35:08

I need to change the date in this format "dd-MM-yyyy HH:mm:ss".

No you don't. What you need to do is stop using string concatenation to insert values into a SQL statement. Use a PreparedStatement.

String query = "INSERT INTO TABLENAME (DATE) values (?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
    stmt.setTimestamp(1, new Timestamp(dt.getMillis()));
    stmt.executeUpdate();
}

java.lang.IllegalArgumentException: Invalid format: "dd-MM-yyyy HH:mm:ss"

2 Answers