Addressing datetime to datetime2 conversion behavior change starting from SQL Server 2016

Starting from SQL Server 2016, the conversion / comparison process of datetime to datetime2 (and vice versa) has been modfied.

Unfortunately, this is a breaking change for many of our existing customers, and some have raised issues asking about this behavior. However, due to how Java only has one datatype (Timestamp) that maps into both datetime and datetime2, this problem cannot be addressed on the driver's end.

There are a couple ways to work around this - one is to ALTER DATABASE COMPATIBILITY_LEVEL.

Syntax:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

This problem won't occur if the server's compatibility level has been set to 120 or below. More details can be found here.

The other way is to convert all datetime columns to datetime2(3) - however, they're not functionally equivalent. Users should be aware of these limitations when working with datetime columns.