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

MartenCatcher · Accepted Answer · 2013-11-20T06:41:24.650

21

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

edited Nov 20 '13 at 06:41

answered Nov 20 '13 at 06:31

MartenCatcher

2,713
8
26
39

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)" – user2951465 Nov 20 '13 at 06:32
use java.sql.Date, not java.util.Date =) – MartenCatcher Nov 20 '13 at 06:34
do you mean something lik this java.sql.Date date = new java.sql.Date(); – user2951465 Nov 20 '13 at 06:36
when i try that it shows me this error "The constructor Date() is undefined" – user2951465 Nov 20 '13 at 06:37
And don't forget to add a check null and empty string! – MartenCatcher Nov 20 '13 at 06:44
Do you import it? import java.text.SimpleDateFormat; – MartenCatcher Nov 20 '13 at 06:48
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)" – user2951465 Nov 20 '13 at 06:55
preparedStatement.setDate(4,dateDB); // dateDB is a object of class java.sql.Date – MartenCatcher Nov 20 '13 at 07:01
@Mark I need help for the same: My sql(5.7) field is TimeStamp, and I am using `getSimpleJDBCTemgetSimpleJdbcTemplate().update(query.toString(), 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? – Aakash Patel Oct 31 '18 at 12:45
@AakashPatel try to manually convert the LastLoginDate to java.sql.Date: `getSimpleJDBCTemgetSimpleJdbcTemplate().update(query.toString(), new Object[] { new java.sql.Date(userProfile.getLastLoginDate().getTime()), userId });` – MartenCatcher Oct 31 '18 at 17:06

Kimball Robinson · Answer 2 · 2015-02-02T23:09:33.733

14

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>

edited Feb 02 '15 at 23:09

answered Feb 02 '15 at 22:41

Kimball Robinson

3,287
9
47
59

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 : https://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.39 – jmcollin92 Sep 19 '16 at 20:58
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. – Kimball Robinson Jun 30 '17 at 21:20

score 4 · Answer 3 · edited Feb 03 '19 at 08:32

4

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.

edited Feb 03 '19 at 08:32

Hamza Khan

107
1
11

answered Nov 20 '13 at 06:34

sumitsabhnani

320
1
6

This is helpful if one cannot update the dependency jar as per @Kimball Robinson's answer. – md_rasler Oct 27 '21 at 21:49

score 1 · Answer 4 · answered Jun 16 '17 at 12:05

1

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.

answered Jun 16 '17 at 12:05

Anantha Raju C

1,780
12
25
35

score 1 · Answer 5 · answered Jul 02 '17 at 22:04

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.

Serhii Maksymchuk · Answer 6 · 2017-02-17T14:42:17.157

If someone will have similar error for entity field with Data type annotated as @Temporal, the solution for me was to replace annotation value TemporalType.TIMESTAMP by TemporalType.TIME:

@Temporal(TemporalType.TIMESTAMP)
private Date dateField;

should be like this:

@Temporal(TemporalType.TIME)
private Date dateField;

Another way to resolve this problem without any changes in code (at least for me) was to run application on higher Tomcat version, hope it will help.

score 0 · Answer 7 · answered Oct 06 '22 at 09:06

Accepted answer only handles date, not datetime. Anyways...

My problem was it didn't accept really old datetime's. I generated date in .net environment, where default date was "1900-01-01 01:01:01". I had to change date to somthing later in time... Ooops.

My error message:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1900-01-01 01:01:01' for column 'added' at row 1

spacether · Answer 8 · 2023-03-13T20:39:05.227

This issue came up for me using mysql and inserting TIMESTAMP data. When times in dropped DST hours were input, mysql was unable to insert a record with that timestamp data. There are mysql docs on how to fix it here: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-time-instants.html

And I fixed it by appending: ?connectionTimeZone=LOCAL&forceConnectionTimeZoneToSession=true onto the db connection url which is solution 2b

score 0 · Answer 9 · answered Mar 16 '23 at 19:17

In my case, I was converting an epoch time value that was precise to the millisecond to a Timestamp object then I was using that object in my PreparedStatement. The solution for me was to simply divide the time value by 1000 so that it was only accurate to the second and it worked.

Data truncation: Incorrect datetime value: ''

9 Answers9

Linked

Related