Question 113090 in Mysql Question posted in
The official MySQL documentation can be found here.

Unable to insert DateTime value to MySQL DB using Java. getting error Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

I am writing code to insert values from CSV files to MySQL DB. one of the SQL columns is DateTime.
I get the value created from CSV and its this: 07/07/10 08:08

Timestamp sqlTimestampCreated = Timestamp.valueOf(String.valueOf(created));
statement.setTimestamp(6, sqlTimestampCreated);

how to convert ‘created’ to Datetime and set it to prepared Statement?

Tags:

2

Answers


  1. 0

    Use SimpleDateFormat class to format your date object in the required format i.e yyyy-mm-dd hh:mm:ss in your case.

    Below is the code to format date using SimpleDateFormat class –

    DateTime yourCsvDate;  //assuming your csv date to be this.
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
simpleDateFormat.format(yourCsvDate);
    Login or Signup to reply.
  2. 0

    tl;dr

    You said:

    how to convert ‘created’ to Datetime and set it to prepared Statement?

    myPreparedStatement.setObject( 
    … , 
    LocalDateTime.parse( 
        "07/07/10 08:08" , 
        DateTimeFormatter.ofPattern( "dd/MM/uu HH:mm" ) 
    ) 
)

    Avoid legacy date-time classes

    You are using terribly flawed date-time classes that were years ago supplanted by the modern java.time classes defined in JSR 310. Avoid using Calendar, either Date, Timestamp, SimpleDateFormat, etc.

    ISO 8601

    I suggest educating the publisher of your data about the virtues in following the ISO 8601 standard for textual representations of date-time values.

    The java.time classes use the standard ISO 8601 formats by default when parsing/generating text.

    The standard format for a date with time but no offset or time zone: YYYY-MM-DD’T’HH:MM.

    java.time

    Parse your input as a LocalDateTime object.

    Define a formatting pattern to match your input, using DateTimeFormatter class.

    String input = "07/07/10 08:08" ;
DateTimeFormatter f = DateTimeFormatter.ofPattern( "dd/MM/uu HH:mm" ) ;
LocalDateTime ldt = LocalDateTime.parse( input , f ) ;

    See this code run at Ideone.com.

    ldt.toString(): 2010-07-07T08:08

    Write to your DATETIME column in MySQL.

    myPreparedStatement.setObject( … , ldt ) ;

    Retrieve.

    LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

    All this has been covered many times on Stack Overflow. Search to learn more.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search