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);
}

Solution 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);

Solution 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>

Solution 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.

Solution 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.

Solution 5

I know this is an old thread, but none of these solutions solved the problem for me. What worked for me was to upgrade hibernate to version 5.2.10.Final (see this SO post).

Running Spring Boot and Spring Data JPA 1.5.4.RELEASE and hibernate 5.2.10.Final.

View more solutions

Comments

[Jhanvi]

Please write some code.

[user2951465]

@jhanvi please check it

[user2951465]

mark when i try that, it shows error stating "The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, Date)"

[MartenCatcher]

use java.sql.Date, not java.util.Date =)

[user2951465]

do you mean something lik this java.sql.Date date = new java.sql.Date();

[user2951465]

when i try that it shows me this error "The constructor Date() is undefined"

[MartenCatcher]

And don't forget to add a check null and empty string!

[MartenCatcher]

Do you import it? import java.text.SimpleDateFormat;

[user2951465]

jus now i did. but nw i get error in "preparedStatement.setDate(4,date);" stating " The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, String)"

[MartenCatcher]

preparedStatement.setDate(4,dateDB); // dateDB is a object of class java.sql.Date

[jmcollin92]

You save my life ! I was using a MySQL 5.7 DB with mysql-connector 3.3.1. Changing to mysql-connector-5.x resolve the issue. Download here : mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.39

[Kimball Robinson]

I believe the version number can be incorrect in either direction, and different people may need different version numbers--depending on your situation. Which is why I didn't note a specific version number in my answer.

[Aakash Patel]

@Mark I need help for the same: My sql(5.7) field is TimeStamp, and I am using getSimpleJDBCTemgetSimpleJdbcTemplate().update(query.toStrin‌​g(), new Object[] { userProfile.getLastLoginDate(), userId }); to update the field. Here, getLastLoginDate returns java.Util.Date so, somehow i am getting exception: Data truncation: Incorrect datetime value: '' for column 'LASTLOGINDATE'. Can you help me with this?

[MartenCatcher]

@AakashPatel try to manually convert the LastLoginDate to java.sql.Date: getSimpleJDBCTemgetSimpleJdbcTemplate().update(query.toStrin‌​g(), new Object[] { new java.sql.Date(userProfile.getLastLoginDate().getTime()), userId });

[md_rasler]

This is helpful if one cannot update the dependency jar as per @Kimball Robinson's answer.