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

Question

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?

Try to parse `INVOICE_DATE` which your mysql database support.`String date = new SimpleDateFormat("yyyy-MM-dd").format(row.getCell(1).getDateCellValue());` — Afsun Khammadli, Jun 03 '15 at 09:39
Have a look here:- http://stackoverflow.com/questions/2400955/how-to-store-java-date-to-mysql-datetime — Naman, Jun 03 '15 at 10:14

score 1 · Answer 1 · answered Jun 03 '15 at 12:57

1

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.

answered Jun 03 '15 at 12:57

Jozef Chocholacek

2,874
2
20
25

Hello Jozef Thank you for your reply. If I want import only month and year what is the code. In my excel file had another column i.e., INVOICE_FOR MONTH and values are "Jan-2013". How can i import this month and year in MYSQL table. – Lee001 Jun 04 '15 at 07:15
And how does your mySQL table look like? Ideally add its `CREATE TABLE` statement to your question. – Jozef Chocholacek Jun 04 '15 at 08:35
Hello Mr. Jozef I got a null pointer exception when my excel row is empty. It means the sheet has 190 rows in between those rows some are empty. In that situation I got java.lang.NullPointerException. Please help me this is my code. – Lee001 Jun 05 '15 at 06:28
@Lee001 I don't see any java code in your comment. Try to paste it e.g. in http://chopapp.com/ (or http://pastebin.com/) and post a link here. – Jozef Chocholacek Jun 05 '15 at 08:06
String sql ="INSERT INTO penality 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 = "'"+CEDGE_INVOICE_NUMBER+"'"; – Lee001 Jun 08 '15 at 04:44
Date INVOICE_DATE = row.getCell(1).getDateCellValue(); java.sql.Date sqlInvoiceDate = new java.sql.Date(INVOICE_DATE.getTime()); String INVOICE_FOR_MONTH=new SimpleDateFormat("MMM-yyyy").format(row.getCell(2).getDateCellValue()); String stringInvoiceForMonth=INVOICE_FOR_MONTH; – Lee001 Jun 08 '15 at 04:45
String LHO=row.getCell(3).getStringCellValue(); double AMOUNT_BEFORE_STAX=(double) row.getCell(4).getNumericCellValue(); double S_TAX=(double) row.getCell(5).getNumericCellValue(); double TOTAL_AMOUNT=(double) row.getCell(6).getNumericCellValue(); double PENALITY=(double) row.getCell(7).getNumericCellValue(); double RECEIVED_AMOUNT=(double) row.getCell(8).getNumericCellValue(); String REMARKS=row.getCell(9).getStringCellValue(); – Lee001 Jun 08 '15 at 04:46
pstm.setString(1, stringInvoiceNumber); pstm.setDate(2, sqlInvoiceDate); pstm.setString(3,stringInvoiceForMonth); pstm.setString(4,LHO); pstm.setDouble(5,AMOUNT_BEFORE_STAX); pstm.setDouble(6,S_TAX); pstm.setDouble(7,TOTAL_AMOUNT); pstm.setDouble(8,PENALITY); pstm.setDouble(9,RECEIVED_AMOUNT); pstm.setString(10, REMARKS); pstm.execute(); – Lee001 Jun 08 '15 at 04:46
This is the logic to import excel data into mysql table. But here I got null pointer exception when the excel row is empty. – Lee001 Jun 08 '15 at 04:48
that means excel is like this _____________________________________________ | In_No | In_Date | |__________________ |__________________ | | TS45628 | 23-Feb-2014 | |__________________ |___________________| | | 18-Jun-2014 | |__________________ | __________________| – Lee001 Jun 08 '15 at 04:59
In this excel had one empty cell. So, here I got "NullPointerException". Help me . – Lee001 Jun 08 '15 at 05:00
@Lee001 Would you mind to use some pasting service? The code in comments is **very hard** to read. – Jozef Chocholacek Jun 08 '15 at 06:12
Hi, Mr.Jozef, I want to display that above inserted data in jsp. Could you please give me code for retrieve data using struts2. – Lee001 Jul 01 '15 at 11:17

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

1 Answers1