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....

It seems to me that 0000-00-00 00:00:00 does not correspond to any actual moment in time, and therefore it fundamentally cannot be converted to a timestamp value. What is the behavior you wanted? Do you want it to convert the invalid ISO date to a default value? — Thom Smith, Sep 01 '09 at 16:53
the next move after i get data(there are more than just one column in table) from table is to write this into XML but(!) i will not write Date value in to XML so i just want to get data from table and after then i will never call getDate() method. — Oleksandr, Sep 01 '09 at 16:58
"Do you want it to convert the invalid ISO date to a default value?" - yes, if it possible... — Oleksandr, Sep 01 '09 at 17:00

score 68 · Accepted Answer · answered Sep 01 '09 at 17:50

68

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.

answered Sep 01 '09 at 17:50

ChssPly76

94,877
24
194
191

Just a side note - this will convert zero dates to nulls during read but it won't convert nulls to zeros during save. I haven't find a way to save "0000-00-00" into a database through hibernate. – serg Sep 01 '09 at 20:04
@serg555 - you're right. I've got to ask, though - why would you ever want to save "0000-00-00" into a database? NULLs exist for precisely this reason, this whole "0000-00-00" abomination is nothing but pain. – ChssPly76 Sep 01 '09 at 21:09
For the same reason why I would ever want to read zero dates - system was designed that way and I have no control over it. – serg Sep 02 '09 at 00:21
@ChssPly76 Thank you so much. – ruseel Feb 08 '13 at 08:57

score 4 · Answer 2 · edited May 07 '13 at 19:23

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.

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

2 Answers2

Linked