str_to_date error in MySQL (Java)

Question

I have the following column value which I am converting and storing in another column

Nov 22 2014 00:00:00 AM

Now I am using the following query to convert it to Date format and store in another column

UPDATE DataNov2014 SET Datee = str_to_date(Date,'%d %b %Y %H:%i:%s');

But I am getting the following exception

Exception in thread "main" java.sql.SQLException: Incorrect datetime value: 'Nov 22 2014 00:00:00 AM' for function str_to_date

Is there any mistake in my query/date format ??
Any help would be appreciated, Thanks

Answer 1

tyr this

UPDATE DataNov2014 SET Date= to_char(Date,'mon dd yyyy mm:ss:hh');

Answer 2

I suggest you use a PreparedStatement and a Date bind parameter. Also, you can use try-with-resources. Putting it all together, something like

String sql = "UPDATE DataNov2014 SET Datee = ?";
DateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    Date theDate = sdf.parse("11-22-2014 00:00:00");
    ps.setDate(1, new java.sql.Date(theDate.getTime()));
    int count = ps.executeUpdate();
    if (count > 0) {
        System.out.printf("Query updated %d rows.%n", count);
    } else {
        System.out.println("Query didn't update any rows");
    }
} catch (Exception e) {
    e.printStackTrace();
}

Answer 3

Assumes your field Datee datatype is Datetime

UPDATE DataNov2014 SET Datee = str_to_date('Nov 22 2014 00:00:00 AM','%M %d %Y %H:%i:%s');