datetime conversion to Java in SQLServer 2016

I have a scenario where I have a table that has a date field with the datetime property.

In the documentation (https://learn.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver15) :

Note that java.sql.Timestamp values can no longer be used to compare values from a datetime column starting from SQL Server 2016. This limitation is due to a server-side change that converts datetime to datetime2 differently, resulting in non-equitable values. The workaround to this issue is to either change datetime columns to datetime2(3), use String instead of java.sql.Timestamp, or change database compatibility level to 120 or below.

I removed the use of timestamp, tried other solutions but none is working.

Could someone indicate any documentation or articles related to this scenario, please?

CarDAO :

String sql = "select c.car, c.color,n" +
             "FROM car cn"
             "WHERE c.createdAt BETWEEN :start and :end";

MapSqlParameterSource params = new MapSqlParameterSource("start", '2021-08-01');
params.addValue("end",'2021-08-10');

return templateMap.entrySet()
                .stream()
                .map(entry -> {
                    NamedParameterJdbcTemplate template = entry.getValue();
                    return template.query(sql, params, (rs, i) -> this.mapCar(rs, entry.getKey()));
                }).flatMap(Collection::stream)
                .collect(Collectors.toList());

mapCar

LocalDateTime createdAt= rs.getTimestamp("createdAt ").toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
    
Car.CarBuilder = Car.build()
[...]
.createdAt(createdAt)

return builder.build();

Generate a stack

 Implicit cast from varchar data type to timestamp is not allowed. Use the CONVERT function to run this query.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Implicit cast from varchar data type to timestamp is not allowed. Use the CONVERT function to run this query.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: A conversão implícita do tipo de dados varchar em timestamp não é permitida. Use a função CONVERT para executar essa consulta.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:654)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:715)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:765)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)

tried other solution using Date and LocalDateTime but without success, can anyone share how they have already solved this problem in their implementations.

Edit :

public class Car { […] private final LocalDateTime createdAt; }

Answer

The solution was to change the date column to datetime2(3), I wanted to bypass it via the application, but given the need, the change was via the database.

ALTER TABLE SAJ.table DROP COLUMN myColumn;

ALTER TABLE SAJ.table ADD myColumndatetime2(3);

Advertisement

Answer