Почему преобразование с DATETIME в DATETIME2 изменилось?

У меня была хранимая процедура, сравнивающая две даты. Из логики моего приложения я ожидал, что они будут равны. Однако сравнение не удалось. Причиной этого было то, что одно из значений было сохранено как DATETIME и должно быть CONVERT -ed до DATETIME2 перед тем, как его сравнить с другим DATETIME2. По-видимому, это изменило его ценность. Я проверил это небольшое испытание:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2='2018-01-18 16:12:25.1130000'
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

Это дало мне следующий результат:

Почему существует разница 333333ns между этими значениями? Я думал, что DATETIME2, как более точный тип, должен иметь возможность точно представлять все значения, которые могут быть сохранены в DATETIME? В документации DATETIME2 указано только:

Когда преобразование происходит из даты и времени, копируются дата и время. Дробная точность увеличивается до 7 цифр.

Никаких предупреждений о преобразовании, добавляющем или вычитающем 333333ns, к значению или от него! Так почему это происходит?

Я использую SQL Server 2016.

edit: Странно, на другом сервере я получаю нулевую разницу. Оба являются SQL Server 2016, но тот, где у меня проблема, имеет уровень совместимости, установленный в 130, тот, где у меня нет его 120. Переключение между ними меняет это поведение.

edit2: DavidG предложил в комментариях, что значение, которое я использую, может быть представлено как DATETIME2, но не DATETIME. Поэтому я изменил свой тест, чтобы убедиться, что значение, присваиваемое @DateTime2, является допустимым значением DATETIME:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2=CONVERT(DATETIME2, @DateTime)
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

Это немного помогает, потому что разница меньше, но все равно не равна нулю:

Ответ 1

A нарушение изменений было представлено в SQL Server 2016 в отношении преобразования и сопоставления даты и времени datetime2. Изменения подробно описаны в этой статье базы знаний.

Таким образом, значения были округлены во время преобразования в SQL 2014 и более ранних версиях, тогда как полная точность рассматривается в настоящее время. Это улучшает производительность, но представляет проблемы при преобразовании и сравнении этих непохожих типов.

Ответ 2

datetime2 является сокращением для datetime2(7), что означает, что вам нужно 7 цифр для дробных секунд (максимум). Попробуйте datetime2(3), если вы хотите что-то ближе к datetime.

Кроме того, имейте в виду, что datetime2(3) более точен, чем datetime. Последний округляет до ближайших 0.000, 0.003 или 0.007 по дизайну.

Ответ 3

Основываясь на это сообщение в блоге MSDN DATETIME премия составляет .00333 секунды, а DATETIME2 (или DATETIME2(7) явно) 100 нс. Поэтому даже сравнивая DATETIME с DATETIME2(3), который, похоже, имеет такую же точность, DATETIME2(3) более точно.

Эта странная 3.33 мс прецессия DATETIME является причиной того, что при сравнении кажущихся одинаковых значений вы можете получить разницу.

Ответ 4

Для меня, когда вы делаете сравнение, вы должны конвертировать данные с высокой точностью в малую точность, чтобы избежать такой "разницы"

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2='2018-01-18 16:12:25.1130000'
SELECT @DateTime, cast(@DateTime2 as datetime), DATEDIFF(NANOSECOND, @DateTime, cast(@DateTime2 as datetime))

Результат