Data Truncation :Incorrect value: error while inserting Date field to Mysql DB

Question

Hi All I am new to this. I was trying to insert Two date fields to Mysql .PFB // DAO class method to add fields.All the fields are coming from jsp form.

 public boolean addIssue(Connection conn, IssueDTO dto)throws ParseException{

    String startDate=dto.getStartDate();
    String endDate=dto.getStartDate();
    System.out.println("the start date from dto--"+dto.getStartDate());
    System.out.println("The end date from dto"+dto.getEndDate());
    DateFormat  format=new SimpleDateFormat("MM/dd/yyyy");

    Date newStartDate=format.parse(startDate);
    Date  newEndDate=format.parse(endDate);


  try{
        String sql="INSERT INTO issue_description (issue,keyword,applicationName,objectName,teamName,startDate,endDate,resolution,priority) values (?,?,?,?,?,?,?,?,?)";
        PreparedStatement statement=conn.prepareStatement(sql);

        statement.setString(1, ""+dto.getIssue());
        statement.setString(2, ""+dto.getKeyword());
        statement.setString(3, ""+dto.getApplicationName());
        statement.setString(4, ""+dto.getObjectName());
        statement.setString(5, ""+dto.getTeamName());
        statement.setString(6, ""+newStartDate);
        statement.setString(7, ""+newEndDate);
        statement.setString(8, ""+dto.getResolution());
        statement.setString(9, ""+dto.getPriority());

        statement.executeUpdate();

below is the error log:::first four lines are the value of dates coming from jsp checked at various stages. But while inserting in DB its changing to 'Wed Oct 14 00:00:00 IST 2015'. Please suggest whats happening here.

 10/14/2015
10/14/2015
the start date fr om dto--10/14/2015
The end date from dto10/14/2015
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value:         'Wed Oct 14 00:00:00 IST 2015' for column 'startDate' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2983)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at  com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at com.sm.dao.IssueDAO.addIssue(IssueDAO.java:65)
at com.controllers.AddIssueController.doPost(AddIssueController.java:72)

Buhake Sindi · Accepted Answer · 2015-10-13 20:56:31Z

1

That is because newStartDate and newEndDate are Date object and, whenever you're concatenating any java object with a String, java will call the object's toString() method to get the object's string representation.

Here is your guilty party code:

    statement.setString(6, ""+newStartDate);
    statement.setString(7, ""+newEndDate);

This is the format for Date.toString():

Converts this Date object to a String of the form: 

 dow mon dd hh:mm:ss zzz yyyy
where:

•dow is the day of the week (Sun, Mon, Tue, Wed, Thu, Fri, Sat). 
•mon is the month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec). 
•dd is the day of the month (01 through 31), as two decimal digits. 
•hh is the hour of the day (00 through 23), as two decimal digits. 
•mm is the minute within the hour (00 through 59), as two decimal digits. 
•ss is the second within the minute (00 through 61, as two decimal digits. 
•zzz is the time zone (and may reflect daylight saving time). Standard time zone abbreviations include those recognized by the method parse. If time zone information is not available, then zzz is empty - that is, it consists of no characters at all. 
•yyyy is the year, as four decimal digits.

I wouldn't convert the date strings into object. Instead, my SQL query would have a STR_TO_DATE function with format of %d/%m/%Y and let MySQL do the date formatting.

answered Oct 13, 2015 at 20:56

Buhake Sindi

89.4k30 gold badges176 silver badges234 bronze badges

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

2 Comments

imsolo Over a year ago

Thanks all. Issue resolved as of now. i just changed the date field in table to "varchar".And inserted the value as a string.

Buhake Sindi Over a year ago

@imsolo, please don't forget to accept the answer that helped.

iperezmel78 · Accepted Answer · 2015-10-13 21:06:44Z

1

If you are using varchar to store date in database, then the data truncation error occurs at Date newStartDate=format.parse(startDate) because you're getting a date with the default format.

If you are using date type to store a date, then you can send the date as string and format it in mysql using str_to_date function.

STR_TO_DATE('10/14/2015', '%m/%d/%Y)

answered Oct 13, 2015 at 21:06

iperezmel78

4557 silver badges22 bronze badges

Comments

TK8 · Accepted Answer · 2015-10-13 21:06:44Z

0

Try the following,

String startDate = dto.getStartDate();
DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
String newStartDate = format.format(format.parse(startDate));

I believe passing String for a Date sql field should work

edited Oct 13, 2015 at 21:06

answered Oct 13, 2015 at 20:55

TK8

1356 bronze badges

Comments

Chris · Accepted Answer · 2015-10-13 21:18:52Z

0

afaik MySQL uses yyyy-MM-dd as default date-string format. Can you try to change the date-string to that format?

By the way, it is better to do an explicit convert in SQL with "STR_TO_DATE" described here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

I haven't seen it first:

newStartDate and newEndDate are Date-Objects. If these Objects are from type java.sql.Date it is the best way to pass it with:

statement.setDate(6, newStartDate);
statement.setDate(7, newEndDate);

edited Oct 13, 2015 at 21:18

answered Oct 13, 2015 at 20:45

Chris

511 silver badge6 bronze badges

Collectives™ on Stack Overflow

Data Truncation :Incorrect value: error while inserting Date field to Mysql DB

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related