CODEWITHSUNDEEP
javasql-serverhibernatejpa
JorgeBPrado
JorgeBPrado

Reputation: 235

Hibernate and SQL Server : Timestamp format must be yyyy-mm-dd hh:mm:ss

I was using hibernate in my app to query data of a MySQL database. Now i'm trying to query instead a SQL Server and I've found this issue about date types.

The table Foo in the SQL server has a column created_at of type datetime

The related pojo foo has a field createdAt of type java.util.Date

@Entity
@Table(name="foo")
public class Foo {

....
    @Column(name="created_at")
    private Date createdAt;

.....
}

But, if I execute a hql like:

Query query = entityManager.createQuery("select f from Foo f");
    query.getResultList();

I get the following exception:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Error al convertir el valor nvarchar al tipo de datos JDBC TIMESTAMP.
    at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:456)
    at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:2007)
    at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:175)
    at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:113)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1982)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1967)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getTimestamp(SQLServerResultSet.java:2377)
    at org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:75)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2969)
    at org.hibernate.loader.plan.exec.process.internal.EntityReferenceInitializerImpl.loadFromResultSet(EntityReferenceInitializerImpl.java:324)
    ... 78 more
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
    at java.sql.Timestamp.valueOf(Timestamp.java:202)
    at com.microsoft.sqlserver.jdbc.DDC.convertStringToObject(DDC.java:306)
    at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:419)
    ... 92 more

How can I solve this in a way that my backend code can work with both MySQL and MS SQL Server?

Upvotes: 3

Views: 9825

Answers (2)

Wiirux_Cruz
Wiirux_Cruz

Reputation: 3

I used java.sql.Date or java.sql.Timestamp and work for me without anotations of @Temporal.

By the way, when you set new values you need to cast them to java.sql.Date or similar.

Upvotes: 0

Naruto
Naruto

Reputation: 4329

Try by using this...

@Column(name="created_at")
@Temporal(TemporalType.TIMESTAMP)
private Date createdAt;

Also show the format in which it is stored in DB.

Upvotes: 6

Related Questions