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

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

You can just add zeroDateTimeBehavior=convertToNull to your connection jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull.

For me, it works perfectly. pls refer to this link for more detail.

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.

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

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.

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