com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: ' '

Question

I have spent the last hour trying alternate versions of inserting date and time via time stamp into a sql database using java.

Date endDate = new Date();    
ptmt.setTimestamp(5, new java.sql.Timestamp(endDate.getTime()));

My create table statement is

CREATE TABLE Location (
  UserID varchar(50),
  Latitude DOUBLE,
  Longitude DOUBLE,
  Altitude DOUBLE,
  TimeInserted timestamp
);

Are there alternative ways of entering the date.

In my prepared statement I tried to have now() within the string, but it looked for the parameter.

String queryString = "INSERT INTO Location(UserID,Latitude,Longitude,Altitude,TimeInserted) VALUES(?,?,?,?,now());";

however it wasn't happy when searching for the 5th parameter.

Any ideas?

I tried making the table date and managed to insert simply the date - however I need the time to order by.

Thanks

I just tried your code with all five (5) parameters and it worked fine for me. There must be something else going on in your code that you haven't shown us. — Gord Thompson
– Gord Thompson, Commented Nov 13, 2015 at 18:51

Ramanlfc · Accepted Answer · 2015-11-12 13:48:24Z

1

however it wasn't happy when searching for the 5th parameter.

String queryString = "INSERT INTO Location(UserID,Latitude,Longitude,Altitude,TimeInserted) VALUES(?,?,?,?,now());";

Your query string has 4 bound parameters but this statement ptmt.setTimestamp(5, new java.sql.Timestamp(endDate.getTime())); is trying to bind a long to the 5th parameter that doesn't exist

answered Nov 12, 2015 at 13:48

Ramanlfc

8,3541 gold badge21 silver badges24 bronze badges

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

1 Comment

bananabreadbob Over a year ago

my query string is actually String queryString = "INSERT INTO LocationTrace(UserID,Latitude,Longitude,Altitude,TimeInserted) VALUES(?,?,?,?,?);"; i just showed how i tried to use now() in the string like you would in sql.

K K · Accepted Answer · 2015-11-13 17:36:06Z

0

Things to remember

public Timestamp(int year, int month, int date, int hour, int minute, int second, int nano)

Deprecated. instead use the constructor Timestamp(long millis)

Date endDate= new Date();
java.sql.Timestamp eDate = new java.sql.Timestamp(enddate.getTime());
long endDatetime= eDate.getTime();

ptmt.setTimestamp(5, endDatetime);

Try this.

answered Nov 13, 2015 at 17:36

K K

991 silver badge8 bronze badges

Collectives™ on Stack Overflow

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: ' '

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related