Data truncation: Incorrect datetime value: ''

Question

Can anyone help me with a sample JSP code to store date in a MySql database through JDBC? When I try to execute the code given below, I get the following exception:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'date' at row 1

How to overcome this problem? Following is my code:

Connection con = null;

String StaffName = request.getParameter("StaffName");
// String subcode = request.getParameter("subcode");
String hourId = request.getParameter("hourId");
if (hourId == null)
    hourId = "";
String day = request.getParameter("day");
if (day == null)
    day = "";
String date = request.getParameter("date");
try {
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/StaffAllocation", "root", "success");

    // PreparedStatement stat = con.PrepareStatement();
    String updateString = "INSERT INTO tblstaffallocation (StaffName,hourId,daysId,date) VALUES (?,?,?,?)";
    PreparedStatement preparedStatement = con.prepareStatement(updateString);

    preparedStatement.setString(1, StaffName);
    preparedStatement.setInt(2, 0);
    preparedStatement.setInt(3, 0);
    preparedStatement.setString(4, date);
} catch (Exception e) {
    out.print(e);
}

Answer 1

To set date to prepared statement you need change type of value:

String date = request.getParameter("date");
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); // your template here
java.util.Date dateStr = formatter.parse(date);
java.sql.Date dateDB = new java.sql.Date(dateStr.getTime());

now convert String date to java.sql.Date and use another method:

preparedStatement.setDate(4,dateDB);

Answer 2

I had a similar error. It turns out I just needed to update the jar version for mysql-connector-java (using maven)

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>...</version>
    </dependency>

Answer 3

Try reformating the date

   String date = new SimpleDateFormat("yyyy-MM-dd")
                             .format(new Date(request.getParameter("date")));

and then insert into the database. Note that request.getParameter("date") should be in format 11/20/2013 for this to work or you can use a similar way to achieve.

Answer 4

Make sure that the Date value that you are trying to insert into the table is exactly in the format defined in the date column of your table.