MSSQL PreparedStatement для smalldatetime возвращает неверный результат

Question

У меня есть таблица в моей базе данных mssql с одним из столбцов как тип данных smalldatetime.

Тип данных smalldatetime не имеет точности секунд. Согласно его документации:

ss - это две цифры в диапазоне от 00 до 59, которые представляют вторую. Значения 29,998 секунд или меньше округляются до ближайшей минуты, а значения 29,999 секунд или более округляются до ближайшей минуты."

У меня есть таблица em_test с описанием столбцов (varchar) и startTime(smalldatetime).

I inserted following entries in table :
    insert into em_test values('e1-2018-08-01 23:59:59', '2018-08-01 23:59:59');
    insert into em_test values('e2-2018-08-02 00:00:00', '2018-08-02 00:00:00');
    insert into em_test values('e3-2018-08-02 01:00:00', '2018-08-02 01:00:00');
    insert into em_test values('e4-2018-08-02 23:59:59', '2018-08-02 23:59:59');
    insert into em_test values('e5-2018-08-03 00:00:00', '2018-08-03 00:00:00');

Если я запускаю запрос: select * from em_test where startTime between '2018-08-02 00:00:00' and '2018-08-02 11:59:59', ниже приведены результаты:

name                    startTime
e1-2018-08-01 23:59:59  2018-08-02 00:00:00 (Rounded up)
e2-2018-08-02 00:00:00  2018-08-02 00:00:00
e3-2018-08-02 01:00:00  2018-08-02 01:00:00
e4-2018-08-02 23:59:59  2018-08-03 00:00:00 (Rounded up)
e5-2018-08-03 00:00:00  2018-08-03 00:00:00

Тот же запрос с использованием подготовленного оператора возвращает другой результат.

name                    startTime
e1-2018-08-01 23:59:59  2018-08-02 00:00:00 (Rounded up)
e2-2018-08-02 00:00:00  2018-08-02 00:00:00
e3-2018-08-02 01:00:00  2018-08-02 01:00:00

Я отладил исходный код mssql-jdbc-6.4.0-jre7.jar, чтобы понять, почему результат, возвращаемый простым SQL-запросом и подготовленным SQL-оператором, отличается. Ниже приведен анализ:

1. Всякий раз, когда должен быть выполнен подготовленный оператор, внутренне создается хранимая процедура во время выполнения, охватывающая запрос. Входные параметры запроса становятся входными параметрами хранимой процедуры.
2. При формировании подготовленного оператора мы устанавливаем значение smalldatetime в качестве метки времени как ps.setTimeStamp('2018-08-01 23:59:59').
3. При формировании хранимой процедуры во время выполнения определяется тип данных входных параметров. TimeStamp сопоставляется с mssql datetime2.
4. Следовательно, в случае datetime2 аргументы '2018-08-02 00:00:00' and '2018-08-02 11:59:59' не округляются и не возвращают два последних результата.
5. Попытался смоделировать процедуру выполнения также с несколькими журналами:

   процедура создания smalldatetimetest @startDate datetime2, @endDate datetime2
   КАК
       выведите 'Start Date в формате Datetime2: ' + Convert(varchar(50), @startDate);
       напечатать 'Конечная дата в формате Datetime2: ' + Convert(varchar(50), @endDate);
       выведите 'Start Date в формате smalldatetime: ' + Convert(varchar(50), cast(@startDate as smalldatetime));
       напечатать 'Конечная дата в формате smalldatetime: ' + Convert(varchar(50), cast(@endDate as smalldatetime));
       выберите * из em_test, где datetime1 между @startDate и @endDate;
   ИДТИ 
   

выполнение exec smalldatetimetest '2018-08-02 00:00:00','2018-08-02 23:59:59' печатает под журналами:

Start Date in Datetime2 format : 2018-08-02 00:00:00.0000000
End Date in Datetime2 format: 2018-08-02 23:59:59.0000000
Start Date in smalldatetime format: Aug  2 2018 12:00AM
End Date in smalldatetime format: Aug  3 2018 12:00AM

Правильно ли мое понимание и есть ли способ обойти несоответствие в случае подготовленного заявления?

Answer 1

Следующий обходной путь, кажется, работает для меня:

Вместо того, чтобы поставлять java.sql.Timestamp значение параметра

ps.setTimestamp(1, ts);

поставь это как строку

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
ps.setString(1, sdf.format(ts));

Это заставляет сгенерированный JDBC запрос использовать nvarchar(4000) параметр вместо datetime2 параметр

exec sp_executesql N'SELECT COUNT(*) AS n FROM #tmp WHERE foo<=@P0        ',N'@P0 nvarchar(4000)',N'2018-08-11 02:59:59.001'

и результаты, по-видимому, совпадают с результатами простого текстового запроса со строковым литералом значения даты / времени, как в вашем вопросе.