We are using SQLServer 2012-2016.
DATETIME data type is part of the key in many tables.
when we execute query with jdbcTemplate.query(query, queryParams, resultSetExtractor)
What is send to SQLServer is :
sp_executesql N'SELECT * FROM [schema_example].[dbo].[tbl1]
WHERE (dtContactGMTStartTime>@P0 OR @P1 is NULL)
ORDER BY dtContactGMTStartTime ASC',
N'@P0 datetime2,@P1 datetime2','2014-01-04 03:10:49.9330000','2014-01-04 03:10:49.9330000'
Parameter datatype is DATETIME2 although the datatype in the database is DATETIME. see documented issue in github . In addition, SQLServer changed it's behavior see link SQLServer improvements in 2016 see below example for different between compatibility level The join between the two issues above causes for sql statements to retrieve different rows for same data in different SQLServer versions.
Is there a workaround, another than changing the datatype in the database to DATETIME2(3)?
example for different between compatibility level
select '20020202 02:02:02.000' AS String, CONVERT(datetime, '20020202 02:02:02.000') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.000')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.000')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.000') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.000')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.001' AS String, CONVERT(datetime, '20020202 02:02:02.001') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.001')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.001')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.001') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.001')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.002' AS String, CONVERT(datetime, '20020202 02:02:02.002') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.002')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.002')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.002') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.002')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.003' AS String, CONVERT(datetime, '20020202 02:02:02.003') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.003')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.003')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.003') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.003')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.004' AS String, CONVERT(datetime, '20020202 02:02:02.004') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.004')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.004')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.004') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.004')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.005' AS String, CONVERT(datetime, '20020202 02:02:02.005') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.005')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.005')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.005') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.005')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.006' AS String, CONVERT(datetime, '20020202 02:02:02.006') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.006')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.006')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.006') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.006')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.007' AS String, CONVERT(datetime, '20020202 02:02:02.007') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.007')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.007')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.007') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.007')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.008' AS String, CONVERT(datetime, '20020202 02:02:02.008') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.008')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.008')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.008') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.008')) AS [Datetime2_3ToDatetime2] union all
select '20020202 02:02:02.009' AS String, CONVERT(datetime, '20020202 02:02:02.009') AS [StringToDatetime], CONVERT(datetime2(3), CONVERT(datetime, '20020202 02:02:02.009')) AS [DatetimeToDatetime2_3], CONVERT(datetime2, CONVERT(datetime, '20020202 02:02:02.009')) AS [DatetimeToDatetime2], CONVERT(datetime2(3), '20020202 02:02:02.009') AS [StringToDatetime2_3], CONVERT(datetime2, CONVERT(datetime2(3), '20020202 02:02:02.009')) AS [Datetime2_3ToDatetime2]
SET COMPATIBILITY_LEVEL = 120
String StringToDatetime DatetimeToDatetime2_3 DatetimeToDatetime2 StringToDatetime2_3 Datetime2_3ToDatetime2
--------------------- ----------------------- --------------------------- --------------------------- --------------------------- ---------------------------
20020202 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000
20020202 02:02:02.001 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000 2002-02-02 02:02:02.001 2002-02-02 02:02:02.0010000
20020202 02:02:02.002 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0030000** 2002-02-02 02:02:02.002 2002-02-02 02:02:02.0020000
20020202 02:02:02.003 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0030000** 2002-02-02 02:02:02.003 2002-02-02 02:02:02.0030000
20020202 02:02:02.004 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0030000** 2002-02-02 02:02:02.004 2002-02-02 02:02:02.0040000
20020202 02:02:02.005 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0070000** 2002-02-02 02:02:02.005 2002-02-02 02:02:02.0050000
20020202 02:02:02.006 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0070000** 2002-02-02 02:02:02.006 2002-02-02 02:02:02.0060000
20020202 02:02:02.007 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0070000** 2002-02-02 02:02:02.007 2002-02-02 02:02:02.0070000
20020202 02:02:02.008 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 2**002-02-02 02:02:02.0070000** 2002-02-02 02:02:02.008 2002-02-02 02:02:02.0080000
20020202 02:02:02.009 2002-02-02 02:02:02.010 2002-02-02 02:02:02.010 2002-02-02 02:02:02.0100000 2002-02-02 02:02:02.009 2002-02-02 02:02:02.0090000
SET COMPATIBILITY_LEVEL = 130
String StringToDatetime DatetimeToDatetime2_3 DatetimeToDatetime2 StringToDatetime2_3 Datetime2_3ToDatetime2
--------------------- ----------------------- --------------------------- --------------------------- --------------------------- ---------------------------
20020202 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000
20020202 02:02:02.001 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.0000000 2002-02-02 02:02:02.001 2002-02-02 02:02:02.0010000
20020202 02:02:02.002 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0033333** 2002-02-02 02:02:02.002 2002-02-02 02:02:02.0020000
20020202 02:02:02.003 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0033333** 2002-02-02 02:02:02.003 2002-02-02 02:02:02.0030000
20020202 02:02:02.004 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 **2002-02-02 02:02:02.0033333** 2002-02-02 02:02:02.004 2002-02-02 02:02:02.0040000
20020202 02:02:02.005 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0066667** 2002-02-02 02:02:02.005 2002-02-02 02:02:02.0050000
20020202 02:02:02.006 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0066667** 2002-02-02 02:02:02.006 2002-02-02 02:02:02.0060000
20020202 02:02:02.007 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0066667** 2002-02-02 02:02:02.007 2002-02-02 02:02:02.0070000
20020202 02:02:02.008 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 **2002-02-02 02:02:02.0066667** 2002-02-02 02:02:02.008 2002-02-02 02:02:02.0080000
20020202 02:02:02.009 2002-02-02 02:02:02.010 2002-02-02 02:02:02.010 2002-02-02 02:02:02.0100000 2002-02-02 02:02:02.009 2002-02-02 02:02:02.0090000