timestamp insert to sql is giving me error

Question

timestamp insert to sql is giving me error :

SQLException: Data truncation: Incorrect datetime value: '' for column 'Date' at row 1 SQLState: 22001 VendorError: 0

~~~~~~~~~~~~~~~~~~this is how it looks like in java:~~~~~~~~~~~~~~~~~~

String sql = "INSERT into complaint (ComplaintID,Date) VALUES" + "(?,?)";   
GetCurrentTimeStamp stamp = new GetCurrentTimeStamp();  
PreparedStatement rss = con.prepareStatement(sql);
rss.setInt(1,newComplaint.getComplaintID());
rss.setTimestamp(2,stamp.getTimeStamp());
newComplaint.setMsgResponse(MessageManager.msgResponse.SUCCESS);
rss.executeUpdate();

~~~~~~~~~~~~~~~~~~this is GetCurrentTimeStamp class:~~~~~~~~~~~~~~~~~~

import java.sql.Timestamp;
import java.util.Date;



public class GetCurrentTimeStamp 
{
    private  java.util.Date date= new java.util.Date();

    public Timestamp getTimeStamp(){
    return new java.sql.Timestamp(date.getTime());
    }
}

~~~~~~~~~~~~~~~~~~this is the Mysql code:~~~~~~~~~~~~~~~~~~

CREATE TABLE `complaint`
 (
  `ComplaintID` int(11) NOT NULL AUTO_INCREMENT,

  `Date` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`ComplaintID`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

please help!!!! thanks!!

Answer 1

Your SQLState is saying it is a character string that is too long. This happens when assigning a date/time/timestamp value to a host variable that is not big enough.

The method doc for one version of setTimestamp:

setTimestamp(int parameterIndex, Timestamp x, Calendar cal)

Example using UTC:

stmt.setTimestamp(1, t, Calendar.getInstance(TimeZone.getTimeZone("UTC")))

Where 1 being the index, t being the timestamp object, and Calendar getting the timezone.

Answer 2

i've solved my problem, these are the changes i made:

String sql = "INSERT into complaint (ComplaintID,Date) VALUES" + "(?,?)";   
PreparedStatement rss = con.prepareStatement(sql);
    rss.setInt(1,newComplaint.getComplaintID());
    SimpleDateFormat sdfDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//dd/MM/yyyy
    java.util.Date date= new java.util.Date();
    Date now = new Date();
    String strDate = sdfDate.format(now);
    Timestamp complainDate= Timestamp.valueOf(strDate);
    rss.setTimestamp(2, complainDate);
    rss.executeUpdate();