java.sql.Timestamp error when inserting into DATETIME

Question

When I try to use a java.sql.Timestamp value to insert into a DATETIME column in MySQL 5.6, I always get this error:

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

The original developer of this project had custom JARs that created the Prepared statements, such that java.util.Date is converted into Timestamp to be inserted into DATETIME and vice versa, as well as handled database connections. Note that this is my assumption since there is no documentation about it. Trying to use a timestamp to insert into DATETIME myself via PreparedStatement had the same results, using something like:

ps.setTimestamp(3, new Timestamp(date.getTime()));

I had to create my own connection so that I can use java.sql.Date to solve this problem. However it zeroes the time value as a result. I used

ps.setDate(3, new java.sql.Date(date.getTime()));

And I get something like

2013-06-27 00:00:00

in the table, since as we know java.sql.Date doesn't store the time.

Surprisingly, on a server that uses MySQL 5.0, this works normally. I'm assuming the millisecond value in Timestamp is causing the problem casting into DATETIME? Since the original works in older versions. Is there any way to resolve this without having to reinstall everything with 5.0?

I can't use java.util.Date in a PreparedStatement. It has to be turned into java.sql.Date first. — Mark M
– Mark M, Commented Jun 27, 2013 at 5:02

Robert Gannon · Accepted Answer · 2013-06-27 04:47:12Z

1

If the problem is milliseconds, explicitly date format your timestamp. If the use case succeeds on MySQL 5.0 and you have different jdbc drivers deployed on 5.0 and 5.6, it is worth testing that driver over MySQL 5.6. Finally, you are not passing an index in your setters and the incorrect datetime value is the empty string. Have you verified in a debugger/logs that you are not attempting to insert an empty string?

edited Jun 27, 2013 at 4:47

answered Jun 27, 2013 at 4:38

Robert Gannon

2534 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark M Over a year ago

Oh, sorry, forgot to include an index when I typed out those sample lines. The code itself has an index and logs show they have value. I am actually using the same jdbc drivers on 5.0 and 5.6. Will DATETIME type take a String? I thought it had to be exclusively a date type like Date or Timestamp.

Mark M Over a year ago

Just tried formatting the timestamp and using that string instead, which worked in my tests. Thanks for the help! There's still the matter of having to make my own database connection for this, but it can't be helped because of the custom JARs. I'll just do my modifications so it works on both 5.0 and 5.6.

Collectives™ on Stack Overflow

java.sql.Timestamp error when inserting into DATETIME

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related