0

I'm uploading the contents of an excel to the db(SQL server). Whenever I upload the file getting Conversion failed when converting date and/or time from character string error when I try to insert data into db

Here's my code

for (int z=2; z < row; z++) 
                {

                        List<String> list = new ArrayList<String>();
                        for(int j=0;j<col;j++)
                        {
                            Cell c = s.getCell(j,z);
                            list.add(c.getContents());
                        }

                        String[] tempList = list.toArray(new String[list.size()]);

                        if(tempList!=null)
                        {


                        String sql = "INSERT INTO helpData (sheet,Domain,ITProduct,What,ByWhichGroup,SPOCPOCName,ByWhen,Impact,HRStatus,Progress,Date,Vendor) VALUES (?,?,?,?,?,?,?,?,?,?,? ,'Amber')";  
                        pstm = (PreparedStatement) conn.prepareStatement(sql);
                        String startDate = tempList[8];
                        SimpleDateFormat sdf1 = new SimpleDateFormat("dd/MM/yy");               
                        java.util.Date date = sdf1.parse(startDate);                            
                        java.sql.Date sqlStartDate = new java.sql.Date(date.getTime()); 
                        pstm.setString(1,sheetName );
                        pstm.setString(2, tempList[0]);
                        pstm.setString(3, tempList[1]);
                        pstm.setString(4, tempList[2]);
                        pstm.setString(5, tempList[3]);
                        pstm.setString(6, tempList[4]);
                        pstm.setString(7,tempList[5]);
                        pstm.setString(8, tempList[6]);
                        pstm.setString(9, tempList[7]);
                        pstm.setString(11, tempList[9]);
                        pstm.setDate(10, sqlStartDate);
                        pstm.execute();
                        }

                    }

I've converted the date to sql date (in the code). But still getting the same. The data will be first put into a tempListthen it'll be added to db. Kindly help!

kishoresrivatsa
  • 13
  • 7
  • 1
    use an universal dateformat that will work on every sql-server, like 'yyyymmdd' for example. More info is here http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput – GuidoG Sep 19 '17 at 10:34
  • thanks for the reply. but there was an error i figured out now. I was updating it to a different column so it was giving error. corrected it working now – kishoresrivatsa Sep 19 '17 at 10:44
  • i hope this will help you : [https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement](https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement) – Rahmat Ihsan Sep 19 '17 at 12:32

0 Answers0