CODEWITHSUNDEEP
javamysqldatetimetimestamp
Mark M
Mark M

Reputation: 1600

java.sql.Timestamp error when inserting into DATETIME

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?

Upvotes: 1

Views: 4439

Answers (1)

Robert Gannon
Robert Gannon

Reputation: 253

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?

Upvotes: 1

Related Questions