JPA creating column LocalDateTime as datetime2 in SQLServer 2008

Question

My entity has this attribute...

@Convert(converter = LocalDateTimeAtributeConverter.class)
@Column(name="dthr_ult_atualizacao")
private LocalDateTime ultimaAtualizacao;

In the server, the column is created by the JPA as:

dthr_ult_atualizacao    (datetime2(7), null)

By code, I save the value below in this column:

2016-05-09T15:20:00.357

When I do a Select direct in the database, the value is correct:

2016-05-09 15:20:00.3570000

But when I recover this value by JPA, the value is wrong:

2016-05-07T15:20:00.357

Note that the day is wrong in two days.

So, if I change manually the data type, all work fine. What is wrong?

My converter:

import java.time.LocalDateTime;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter
public class LocalDateTimeAtributeConverter implements AttributeConverter<LocalDateTime, java.sql.Timestamp> {

    @Override
    public java.sql.Timestamp convertToDatabaseColumn(LocalDateTime entityValue)    
    {
        if (entityValue != null) {
           return java.sql.Timestamp.valueOf(entityValue);
        }
        return null;
     }

     @Override
     public LocalDateTime convertToEntityAttribute(java.sql.Timestamp   databaseValue) {
         if (databaseValue != null) {
             return databaseValue.toLocalDateTime();
         }
        return null;
     }
   }

I am using Microsofr jdbc42 with wildfly 9

Rodrigo Menezes · Accepted Answer · 2016-06-21T20:27:04.547

are you using an old JDBC drive?

There are a issue with SQL Server's JDBC Version 3 with Java7: https://support.microsoft.com/kb/2652061

You can get new version here: https://www.microsoft.com/en-us/download/details.aspx?id=11774

I made some tests and got this results:

Date - JDBC Version

2016-06-21 16:19:00.383 - 4.2.6420.100

2016-06-19 16:19:38.603 - 3.0.1301.101

Teste code:

public static void main(String[] args) throws Exception {
    String url = "jdbc:sqlserver://localhost\\SQLEXPRESS;databasename=teste_date";
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection connection = DriverManager.getConnection(url,"sa", "password");

    Date time = Calendar.getInstance().getTime();

    PreparedStatement psDel = connection.prepareStatement("delete from teste");
    psDel.executeUpdate();
    psDel.close();

    PreparedStatement psInsert = connection.prepareStatement("insert into teste values (? , ?)");
    psInsert.setInt(1, 1);
    psInsert.setTimestamp(2, new Timestamp(time.getTime()));
    psInsert.executeUpdate();
    psInsert.close();

    PreparedStatement ps = connection.prepareStatement("select * from teste");
    ResultSet rs = ps.executeQuery();
    String driverVersion = connection.getMetaData().getDriverVersion();
    while (rs.next()) {
        Timestamp date = rs.getTimestamp(2);
        System.out.println(date + " - " + driverVersion);
    }
    rs.close();
    ps.close();

    connection.close();
}

JPA creating column LocalDateTime as datetime2 in SQLServer 2008

1 Answers1