DateTime2 против DateTime в SQL Server

DATETIME2 имеет диапазон дат "0001 / 01 / 01" через "9999 / 12 / 31" в то время как DATETIME тип поддерживает только год 1753-9999.

также, если вам нужно, DATETIME2 может быть более точным с точки зрения времени; DATETIME ограничен 3 1/3 миллисекунды, в то время как DATETIME2 можно с точностью до 100 наносекунд.

оба типа карты System.DateTime в .NET-никакой разницы нет.

если у вас есть выбор, я бы рекомендовал использовать DATETIME2 всякий раз, когда это возможно. Я не вижу преимущества использования DATETIME (за исключением обратной совместимости) - у вас будет меньше проблем (с датами, находящимися вне диапазона и такими хлопотами).

плюс: если вам нужна только дата (без времени), дата использования - это так же хорошо, как DATETIME2 и экономит вам место, тоже! :- ) То же самое касается только времени - используйте TIME. Вот для чего существуют эти типы!

datetime2 выигрывает в большинстве аспектов, за исключением (совместимость старых приложений)

1. больше диапазон значений
2. лучше точность
3. меньше место хранения (если указана необязательная пользовательская точность)

пожалуйста, обратите внимание на следующие моменты

• синтаксис
  • datetime2 [(точность дробных секунд= > смотреть Ниже Размера Хранилища)]
• точность, масштаб
  • 0 до 7 чисел, с точностью 100нс.
  • точность по умолчанию составляет 7 цифр.
• Размер Хранилища
  • 6 байт для точности менее 3;
  • 7 байт для точности 3 и 4.
  • все остальные требуется 8 байт.
• DateTime2 (3) имеют одинаковое число цифры как DateTime, но использует 7 байт памяти вместо 8 байт (SQLHINTS-DateTime Vs DateTime2)
• подробнее о datetime2 (Transact-SQL MSDN article)

Источник изображения : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - внедрение и обслуживание Глава 3: таблицы - > Урок 1: создание таблиц - > страница 66

Я согласен с @marc_s и @Adam_Poward -- DateTime2 является предпочтительным методом продвижения вперед. Он имеет более широкий диапазон дат, более высокую точность и использует равное или меньшее хранение (в зависимости от точности).

одна вещь, которую обсуждение пропустило, однако...
@Marc_s государства: Both types map to System.DateTime in .NET - no difference there. Это правильно, однако обратное неверно...и это имеет значение при выполнении поиска диапазона дат (например, "найти мне все записи, измененные на 5/5/2010").

.NET-версия Datetime имеет сходную дальность и точность DateTime2. При сопоставлении .net Datetime вплоть до старого SQL DateTime an неявное округление происходит. Старый SQL DateTime С точностью до 3 миллисекунд. Это значит, что 11:59:59.997 - это так близко, как вы можете добраться до конца дня. Ничего более округляется до следующего дня.

попробуйте это :

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

избежать этого неявного округления является важной причиной для перемещения в datetime2. Неявное округление дат явно вызывает путаницу:

• странное поведение datetime в SQL Server
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 и миллисекунды
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx

DateTime2 наносит ущерб, если вы являетесь разработчиком доступа, пытающимся написать сейчас() в рассматриваемое поле. Просто сделал миграцию Access - > SQL 2008 R2, и он поместил все поля datetime в качестве DateTime2. Добавление записи с помощью Now () в качестве значения bombed out. Это было хорошо на 1/1/2012 2: 53:04 вечера, но не на 1/10/2012 2:53: 04 вечера.

Как только характер сделал разницу. Надеюсь, это кому-то поможет.

вот пример, который покажет вам различия в размере хранилища (байты) и точности между smalldatetime, datetime, datetime2(0) и datetime2 (7):

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

возвращает

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

поэтому, если я хочу сохранить информацию до второй - но не до миллисекунды - я могу сохранить 2 байта каждый, если я использую datetime2(0) вместо datetime или datetime2(7).

почти все ответы и комментарии были тяжелыми на плюсы и легкие на минусы. Вот краткое изложение всех плюсов и минусов до сих пор плюс некоторые важные минусы (в № 2 ниже), которые я видел только один раз или вообще не видел.

1. плюсы:

1.1. Более ISO-совместимый (ISO 8601) (хотя я не знаю, как это вступает в игру на практике).

1.2. Больше диапазона (1/1/0001 до 12/31/9999 против 1/1/1753-12/31/9999) (хотя дополнительный диапазон, все до 1753 года, скорее всего, не будет использоваться, за исключением ex., в историческом, астрономическом, геологическом и др. приложения.)

1.3. Точно соответствует диапазону .NET DateTime диапазон типа (хотя оба преобразовывают назад и вперед без специального кодирования, если значения находятся в пределах диапазона и точности целевого типа, за исключением Con # 2.1 ниже, произойдет ошибка / округление).

1.4. Больше точности (100 наносекунд ака 0,000, 000, 1 сек. против 3,33 миллисекунд ака 0.003, 33 сек.) (хотя дополнительная точность, скорее всего, не будет использоваться, за исключением ex., в инженерных / научных приложениях).

1.5. При настройке для как (как в 1 миллисекундах не "то же самое" (как в 3,33 миллисекундах), как утверждал Иман Абиди) точность как DateTime, использует меньше места (7 против 8 байт), но тогда, конечно, вы потеряете преимущество точности, которое, вероятно, является одним из двух (другой диапазон) наиболее рекламируемым, хотя, вероятно, ненужным выгоды.)

1. плюсы:

2.1. При передаче параметра в .NET SqlCommand необходимо указать System.Data.SqlDbType.DateTime2 Если вы можете передавать значение вне SQL Server DateTime’диапазон и/или точность, потому что по умолчанию System.Data.SqlDbType.DateTime.

2.2. Невозможно неявно / легко преобразовать в числовое значение с плавающей запятой (количество дней с момента минимальной даты-времени), чтобы выполнить следующие действия с ним в выражениях SQL Server с использованием числовых значений и операторы:

2.2.1. добавить или вычесть количество дней или частичных дней. Примечание: С Помощью DateAdd функция в качестве обходного пути не является тривиальной, когда вам нужно рассмотреть несколько, если не все части даты-времени.

2.2.2. возьмите разницу между двумя датами для целей расчета "возраста". Примечание: Вы не можете просто использовать SQL Server DateDiff функция вместо этого, потому что он не вычисляет age как и большинство людей ожидали бы в том, что если две даты-раз случается пересечение календарной / часовой границы даты-времени единиц, указанных, если даже для крошечной части этой единицы, она вернет разницу как 1 из этой единицы против 0. Например,DateDiff на Day 's двух дат-раз только 1 миллисекунда друг от друга вернет 1 против 0 (дней), если эти даты-раз в разные календарные дни (т. е." 1999-12-31 23: 59: 59.9999999 " и "2000-01-01 00:00:00.0000000"). Та же самая 1 миллисекундная разница даты-времени, если ее переместить так, чтобы они не пересекали календарный день, будет верните "DateDiff" в Day ' s 0 (дней).

2.2.3. возьмите Avg даты-времени (в агрегированном запросе), просто Преобразуя сначала в "Float", а затем обратно в DateTime.

Примечание: Для преобразования DateTime2 к числовому, вы должны сделать что-то вроде следующей формулы, которая по-прежнему предполагает, что ваши значения не меньше, чем в 1970 году (что означает, что вы теряете весь дополнительный диапазон плюс еще 217 лет. Примечание: Вы не можете быть в состоянии просто настроить формула для обеспечения дополнительного диапазона, потому что вы можете столкнуться с проблемами числового переполнения.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 – источник:"https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html"

конечно, вы могли бы также Cast до DateTime первый (и при необходимости обратно в DateTime2), но вы потеряете точность и дальность (до 1753 года) преимущества DateTime2 и DateTime которые, вероятно, являются самыми большими 2, а также в то же время время, вероятно, 2 наименее вероятно, что вызывает вопрос, зачем использовать его, когда вы теряете неявные / легкие преобразования в числовые числа с плавающей запятой (#дней) для сложения / вычитания / " возраст "(против DateDiff)/Avg calcs преимущество, которое является большим в моем опыте.

кстати,Avg дата-время (или по крайней мере должны be) важный случай использования. a) кроме использования в получении средней продолжительности, когда дата-время (начиная с общей базовой даты-времени) используется для представление продолжительности (обычная практика), b) также полезно получить статистику типа панели мониторинга о том, что среднее время-дата находится в столбце даты-времени диапазона / группы строк. в) стандарт (или по крайней мере должны быть стандартным) специальный запрос для мониторинга / устранения неполадок значений в столбце, который может быть недействительным когда-либо / больше и / или, возможно, должен быть устаревшим, должен перечислять для каждого значения количество вхождений и (если доступно)Min,Avg и Max дата-штампов времени связано с этим значением.

интерпретация строк даты в datetime и datetime2 может быть по-разному тоже, при использовании не-нас DATEFORMAT настройки. Е. Г.

set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2

возвращает 2013-05-06 (т. е. 6 мая) для datetime и 2013-06-05 (т. е. 5 июня)datetime2. Однако, с dateformat значение mdy, и @d и @d2 возвращение 2013-06-05.

The datetime поведение кажется не в ладах с Примечание на SET DATEFORMAT в которой говорится: некоторые строки символов форматы, например ISO 8601, интерпретируются независимо от параметра DATEFORMAT. Очевидно, это не так!

пока я не был укушен этим, я всегда думал, что yyyy-mm-dd дат будет сделано правильно, независимо от языка и региональных параметров.

пока есть увеличение точность С datetime2 некоторые клиенты не поддерживают дата,времени или datetime2 и заставить вас преобразовать в строковый литерал. В частности, Microsoft упоминает" нижний уровень " ODBC, OLE DB, JDBC и sqlclient проблемы с этими типами данных и имеет диаграммы показывает, как каждый может сопоставить тип.

, Если значение совместимость за точностью, используйте datetime

по данным в этой статье, Если вы хотите иметь такую же точность DateTime с помощью DateTime2 вы просто должны использовать DateTime2(3). Это должно дать вам ту же точность, занять на один байт меньше и обеспечить расширенный диапазон.

Я только что наткнулся на еще одно преимущество для DATETIME2: Это позволяет избежать ошибки в Python adodbapi модуль, который взрывается, если стандартная библиотека datetime передается значение, которое имеет ненулевые микросекунды для DATETIME столбец, но работает нормально, если столбец определяется как DATETIME2.

Старый Вопрос... Но я хочу добавить кое-что еще не заявленное здесь никем... (Примечание: это мое собственное наблюдение, поэтому не просите никаких ссылок)

Datetime2 быстрее при использовании в критериях фильтра.

TLDR:

в SQL 2016 у меня была таблица со ста тысячами строк и столбцом datetime ENTRY_TIME, потому что требовалось хранить точное время до секунд. При выполнении сложного запроса со многими соединениями и подзапрос, когда я использовал предложение where как:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

запрос был прекрасен изначально, когда были сотни строк, но когда количество строк увеличилось, запрос начал давать эту ошибку:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

Я удалил предложение where, и неожиданно запрос был выполнен за 1 сек, хотя теперь все строки для всех дат были извлечены. Я запускаю внутренний запрос с предложением where, и это заняло 85 секунд, а без предложения where это заняло 0,01 секунды.

я наткнулся на многие темы здесь для этой проблемы как производительность фильтрации datetime

я оптимизировал запрос немного. Но реальная скорость, которую я получил, была путем изменения столбца datetime на datetime2.

теперь тот же запрос, который ранее истекал, занимает меньше секунды.

ура

Select ValidUntil + 1
from Documents

приведенный выше SQL не будет работать с полем DateTime2. Он возвращает и ошибка "тип операнда clash: datetime2 несовместим с int"

добавление 1 для получения следующего дня-это то, что разработчики делали с датами в течение многих лет. Теперь у Microsoft есть супер новое поле datetime2, которое не может обрабатывать эту простую функциональность.

"давайте использовать этот новый тип, который хуже, чем старый", я так не думаю!

Я думаю, что DATETIME2-это лучший способ сохранить дату, потому что он имеет большую эффективность, чем значение datetime. В SQL Server 2008 можно использовать DATETIME2, он хранит дату и время, занимает 6-8 байт для хранения и имеет точность 100 наносекунд. Поэтому любой, кто нуждается в большей точности времени, захочет DATETIME2.