Getting the exception like "Cannot convert value '0000-00-00 00:00:00' from column 12 to TIMESTAMP"

Question

Previously the column Data type is Date now I am changed to Timestamp Now if I tried to run the program am getting them exception

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 12 to TIMESTAMP. at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1298) at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:124) at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6610) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5928) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5966) at org.hibernate.type.TimestampType.get(TimestampType.java:30) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154) at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308) at org.hibernate.loader.Loader.getRow(Loader.java:1206) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580) at org.hibernate.loader.Loader.doQuery(Loader.java:701) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 40 more

Answer 1

You can Use UNIX_TIMESTAMP(date) function to explicitly convert the value to TIMESTAMP.

Answer 2

Make sure that in you java code the field type is java.sql.Timestamp

Answer 3

0000-00-00 00:00:00 is outside the range of a TIMESTAMP value (in fact, it won't work with a DATE field either). From the MySQL manual:

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.