Cannot convert '0000-00-00 00:00:00' to TIMESTAMP

Question

the field definition

 /** Date. */
  @Column(columnDefinition = "datetime")
  private Date date;

setter

public void setDate(final Date date) {
    DateFormat dfmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    try {
      this.date = dfmt.parse(dfmt.format(date));
    } catch (ParseException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

  }

Does anyone have idea how to convert "zero date" into proper value ? Because i have error:

Cannot convert value '0000-00-00 00:00:00' from column 13 to TIMESTAMP

And even if i set "default" field and setter like this:

/** Date. */
      @Column
      private Date date;


public void setDate(final Date date) {
      this.date = date;   
  }

I'll still have the same problem....

Solution 1

I'm going to take a wild guess here that you're using MySQL :-) It uses "zero dates" as special placeholder - unfortunatelly, JDBC can not handle them by default.

The solution is to specify "zeroDateTimeBehavior=convertToNull" as parameter to your MySQL connection (either in datasource URL or as an additional property), e.g.:

jdbc:mysql://localhost/myDatabase?zeroDateTimeBehavior=convertToNull

This will cause all such values to be retrieved as NULLs.

Solution 2

I don't understand the point in your code, where you format then parse again a date. This seems like an identical operation. Maybe you could elaborate?

---

If you want to give a default value to a date, you could do :

/** Jan 1, 1970 ; first moment in time in Java */
private static final Date NO_DATE = new Date(0L);

private Date date;

public void setDate(final Date date) {
     if (date == null) {
         this.date = NO_DATE;
     } else {
         this.date = date;
     }
}

Note : the annotation are optionnal, here I didn't add them.

In this code, you could substitute what you want to the condition, and to the default value.

You could also add a similar setter, that would take a String argument, and check for your special "00000..." value. This would allow for setting the field either with a Date, or with a String.