#datetime #sql-server-2016 #mssql-jdbc #datetime2 #compatibility-level
#datetime #sql-server-2016 #mssql-jdbc #datetime2 #уровень совместимости
Вопрос:
Мы используем SQLServer 2012-2016. Тип данных DATETIME является частью ключа во многих таблицах. когда мы выполняем запрос с jdbcTemplate.query(query, queryParams, resultSetExtractor) тем, что отправляется на SQLServer, является :
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'
Тип данных параметра — DATETIME2, хотя тип данных в базе данных — DATETIME.
смотрите документированную проблему в github .
Кроме того, SQLServer изменил свое поведение, см. ссылку Улучшения SQLServer в 2016, смотрите ниже пример для определения различий между уровнями совместимости
Объединение двух вышеуказанных проблем приводит к тому, что операторы sql извлекают разные строки для одних и тех же данных в разных версиях SQLServer.
Существует ли обходной путь, отличный от изменения типа данных в базе данных на DATETIME2 (3)?
пример для разных уровней совместимости
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