CODEWITHSUNDEEP
javamysqldatejdbc
Lee001
Lee001

Reputation: 19

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 11 00:00:00 IST 2013' for column 'INVOICE_DATE' at row 1

I've an excel sheet Book.xls like below:

   _________________________________________________________________
   |                                         |                      |
   |        INVOICE                          |        INVOICE_DATE  |
   |_________________________________________|______________________|
   |                                         |                      |
   |C-EDGE/SBI//BHO/(ATM)-013/2012–2013      |   11-Feb-2013        |
   |_________________________________________|______________________|
   |C-EDGE/SBI//BANG/(ATM)-013/2012–2013     |   13-Aug-2014        |
   |_________________________________________|______________________|

I am trying to insert Date from excel to MySQL table as below:

try {
    POIFSFileSystem fs = new POIFSFileSystem(input);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    Row row;
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        RichTextString CEDGE_INVOICE_NUMBER = row.getCell(0).getRichStringCellValue();
        Date INVOICE_DATE = row.getCell(1).getDateCellValue();
        System.out.println(INVOICE_DATE);
        String sql = "INSERT INTO tablename VALUES('" + CEDGE_INVOICE_NUMBER + "','" + INVOICE_DATE + "')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
    }
    con.commit();
    pstm.close();
    con.close();
    input.close();
    System.out.println("Success import excel to mysql table");
} catch (ClassNotFoundException e) {
    System.out.println(e);
} catch (SQLException ex) {
    System.out.println(ex);
} catch (IOException ioe) {
    System.out.println(ioe);
}

However, I got an exception:

"com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 11 00:00:00 IST 2013' for column 'INVOICE_DATE' at row 1".

How is this caused and how can I solve it?

Upvotes: 0

Views: 2246

Answers (1)

Jozef Chocholacek
Jozef Chocholacek

Reputation: 2924

When using PreparedStatement, use ? placeholders for values; then you can use setDate method of the PreparedStatemet. E.g. in your code:

String sql ="INSERT INTO tablename VALUES(?,?)";
pstm = (PreparedStatement) con.prepareStatement(sql);

for(int i=1; i<=sheet.getLastRowNum(); i++){
    row = sheet.getRow(i);
    RichTextString CEDGE_INVOICE_NUMBER = row.getCell(0).getRichStringCellValue();
    String stringInvoiceNumber = ... // convert RichTextString to normal String

    Date INVOICE_DATE = row.getCell(1).getDateCellValue();
    // you have to use java.sql.Date instead of java.util.Date in PreparedStatement
    java.sql.Date sqlInvoiceDate = new java.sql.Date(INVOICE_DATE.getTime());

    pstm.setString(1, stringInvoiceNumber);
    pstm.setDate(2, sqlInvoiceDate);

    pstm.execute();

    ...    
}

See e.g. http://tutorials.jenkov.com/jdbc/preparedstatement.html for more info.

Upvotes: 1

Related Questions