Spring Boot 3 and Hibernate 6 migration error: The conversion from datetime2 to DATETIMEOFFSET is unsupported

Question

I upgraded my Spring Boot project to Spring Boot 3.0.0 and Hibernate 6.x. The application starts without any error and when I access any table information that has Instant date type, I get below error

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from datetime2 to DATETIMEOFFSET is unsupported.

I am using SQL Server 2016 with Spring Data 3.0.0

Answer 1

Hibernate 6 changed the way Instant date type is stored.

Instant now maps to the type code SqlType.TIMESTAMP_UTC by default, which maps to the SQL type timestamp with time zone if possible, and falls back to timestamp. Due to this change, schema validation errors could occur on some databases.

The migration to timestamp with time zone might require a migration expression like cast(old as timestamp with time zone). To retain backwards compatibility, configure the setting hibernate.type.preferred_instant_jdbc_type to TIMESTAMP.

Hibernate 5 stores the Java Instant in the format of 2020-08-03 14:54:40.0000000 and Hibernate 6 adds Timezone which would become 2020-08-03 14:54:40.0000000 +00:00

I am not a fan of new implementation, but if you like to change it, add below line to application.properties file in spring boot

spring.jpa.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP

See Hibernate 6 migration guide for details.