Ошибка при преобразовании типа данных varchar к smalldatetime

I’ve had the same problem and determined that this issue arises because SQL Server does not perform comparisons on characters converted to integers in an identical manner. In my test, I’ve found that some comparisons of converted characters, such as the exclamation point, will return type conversion errors, while other comparisons of converted characters, such as the space, will be determined to be out of range.

This sample code tests the different possible scenarios and presents a solution using nested REPLACE statements. The REPLACE determines if there are any characters in the string that are not numerals or the slash, and, if any exist, the length of the string will be greater than zero, thereby indicating that there are ‘bad’ characters and the date is invalid.

DECLARE @str varchar(10)
SET @str = '12/10/2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/10/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012'
    IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
        PRINT @str+': Passed Test'
        ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string

Output:

--Output
--12/10/2012: Passed Test
--Number of characters in 12/10/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 0

--Msg 245, Level 16, State 1, Line 4
--Conversion failed when converting the varchar value '!0' to data type int.
--Number of characters in 12/!0/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 1

--12/  /2012: Failed Test
--Number of characters in 12/  /2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 2

Перевод статьи подготовлен специально для студентов курса «MS SQL Server разработчик».

---

Содержание

• Ошибка #1: Предполагать, что значения даты и времени хранятся в виде форматированных строк
• Ошибка #2: Забыть о людях, которые живут в других частях света
• Ошибка #3: Снова забыть о людях, которые живут в других частях света
• Ошибка #4: Относиться к DATETIME2 только как к более точному DATETIME
• Ошибка #5: Игнорировать округление даты/времени
• Ошибка #6: Делать лишнюю работу для удаления времени из полной даты
• Ошибка #7: Не понимать как работает функция DATEDIFF
• Ошибка #8: Небрежно относиться к условиям поиска
• Ошибка #9: Забыть о диапазонах в типах данных для даты/времени
• Ошибка #10: Не использовать преимуществ функций работы с датой и временем

Ошибка #1: Предполагать, что значения даты и времени хранятся в виде форматированных строк

Многие из ошибок, связанных с обработкой даты и времени, часто происходят из-за непонимания того, как SQL Server хранит эти значения. (Документация SQL Server здесь тоже не сильно помогает, так как не углубляется в эту тему.)

Начинающие T-SQL-разработчики часто предполагают, что значения даты/времени хранятся в человекочитаемом виде, таком как «05-07-2015 10:05:23.187». Но это не так. А если точнее, то SQL Server хранит дату/время в виде одного или нескольких целых чисел (в зависимости от типа данных). В некоторых источниках говорится, что данные хранятся в виде чисел с плавающей точкой, но это ничего не меняет (концепция остается та же самая — мы говорим хранении дат в виде чисел, а не в виде форматированных строк).

Давайте начнем с типа DATETIME. Согласно документации SQL Server, значение DATETIME хранится в виде двух целых чисел. Первое целое число представляет день, а второе — время. Диапазон дней от 1 января 1753 года до 31 декабря 9999 года. Времени — от 00:00:00.000 до 23:59:59.997. Значением по умолчанию является 1900-01-01 00:00:00.000.

Значение по умолчанию для даты особенно важно. 1 января 1900 года считается нулевым днем.
Более ранние даты представляются отрицательными целыми числами, а более поздние — положительными целыми. Например, 1 января 1899 года — это день -365, а 1 января 1901 года — день 365. Что касается времени, то SQL Server начинает с нуля и увеличивает значение для каждой 0,003 секунды после полуночи. Это означает, что время 00:00:00.003 хранится как 1, а время 00:00:01.000 как 300.

Поначалу это все может запутать, потому что при получении значения DATETIME мы видим нечто другое. Например, начнем с простой переменной DATETIME:

DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT @a;

Как мы и ожидали, оператор SELECT возвращает значение в следующем виде:

2015-05-07 10:05:23.187

Чтобы получить значение, которое действительно хранится, мы должны преобразовать его в тип VARBINARY:

DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT CONVERT(VARBINARY(8), @a);

Неудивительно, что результат теперь выглядит совсем по-другому:

0x0000A49100A6463C

Поскольку SQL Server хранит значение DATETIME в виде двух целых чисел (int), то его размер составляет 8 байт (каждое число 4 байта). Первые 4 байта (0000A491) представляют собой дату, а последние 4 байта (00A6463C) время. Зная это, мы можем использовать функцию SUBSTRING, чтобы посмотреть только дату или только время:

DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4);

Теперь оператор SELECT возвращает только байты, представляющие дату:

0x0000A491

Можем то же самое сделать для времени и преобразовать VARBINARY в INT. Давайте соберем все вместе и посмотрим как хранится исходное значение DATETIME:

DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

В следующей таблице показаны результаты SELECT:

Результаты показывают, что с 1 января 1900 года прошло 42 129 дней, а с полуночи прошло более 10 миллионов долей секунды.

Теперь давайте переведем часы примерно на 188 лет назад :

DECLARE @a DATETIME = '1827-12-03 22:15:11.297'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

На этот раз до 1 января 1900 года было 26 327 дней, а время — более 24 миллионов тиков:

Теперь установим дату и время по умолчанию (день 0):

DECLARE @a DATETIME = '1900-01-01 00:00:00.000'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

Как и ожидалось, VARBINARY и INT содержат ноль:

Теперь, просто убедиться, что вы поняли, посмотрим еще один пример с DATETIME. К значению по умолчанию прибавляем один день и одну секунду:

DECLARE @a DATETIME = '1900-01-02 00:00:01.000'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

На данный момент результаты должны быть вполне ожидаемыми. Дата — 1, время — 300:

Сейчас у вас должно быть довольно хорошее представление о том, как хранятся значения DATETIME. Однако, для других типов даты/времени SQL Server использует несколько иной подход. Давайте посмотрим на тип данных DATETIME2, объявленный с точностью по умолчанию (7):

DECLARE @b DATETIME2 = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

На этот раз наши результаты выглядят немного иначе, чем для DATETIME:

0x07B4854E9254EC390B

Для DATETIME2 SQL Server использует первый байт для хранения точности времени (07), последние три байта для хранения даты (EC390B), и все что между ними для хранения времени (B4854E9254), длина которого может изменяться в зависимости от указанной точности. Типы данных DATE и TIME работают аналогично. Например, сохраним это же значение в DATE:

DECLARE @b DATE = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

Наши результаты совпадают с частью даты, возвращенной в предыдущем примере:

0xEC390B

И то же самое для типа TIME:

DECLARE @b TIME = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

Результаты совпадают с частью времени, возвращенной в примере с DATETIME2:

0x07B4854E9254

Теперь изменим точность и значение времени:

DECLARE @b DATETIME2(4) = '2015-05-07 00:00:00.0001'
SELECT CONVERT(VARBINARY(10), @b);

Оператор SELECT возвращает следующие результаты:

0x0401000000EC390B

Обратите внимание, что первый байт хранит точность (04), после него идет уже меньшее количество байтов, связанных со временем (01000000). К сожалению, логика, которую SQL Server использует для хранения даты и времени для типов DATETIME2, DATE и TIME не так проста, как для DATETIME, и углубление в эту логику выходит за рамки данной статьи, но, по крайней мере, вы можете увидеть отдельно байты, представляющие дату и время, и получить некоторое представление о том, что происходит.

Смысл всего этого не в том, чтобы запомнить точные механизмы SQL Server, используемые для хранения даты и времени, а скорее для общего понимания того, как хранятся даты и что это не просто строки определенного формата. Понимание этого поможет вам лучше решать различные вопросы, возникающие при работе с датой/временем.

Ошибка #2: Забыть о людях, которые живут в других частях света

T-SQL может быть воспринят как универсальный язык, по крайней мере, в рамках базы данных, однако, это не касается параметров SQL Server. Довольно часто установленный экземпляр сконфигурирован так, чтобы обслуживать только локальных (местных) пользователей. Но это часто приводит к проблемам при работе с датой/временем. Хотя SQL Server хранит даты в виде одного или нескольких целых чисел, но за кулисами он часто преобразует их из целых чисел в строковые читаемые форматы и наоборот, чтобы нам не приходилось работать с датами, которые выглядят как 15481099 или как 24033389.

Для этого в SQL Server есть несколько параметров и правил, которые определяют как интерпретировать строковые значения даты/времени. Давайте посмотрим несколько примеров. В первом случае мы установим язык british (британский английский) и преобразуем значение VARCHAR в DATETIME:

SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';
SELECT CAST(@a AS DATETIME);

Как и ожидалось, оператор SELECT возвращает следующие результаты:

2015-05-19 10:11:12.000

Теперь давайте установим язык на US English (американский английский) и попробуем преобразовать значение:

SET LANGUAGE us_english;
DECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';
SELECT CAST(@a AS DATETIME);

На этот раз SQL Server возвращает ошибку:

Msg 242, Level 16, State 3, Line 730
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 730
Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

Проблема здесь в формате даты: день-месяц-год. Это прекрасно работает, когда SQL Server сконфигурирован для языка british, но не работает для US English. Когда SQL Server, настроенный на US English, видит данные, он предполагает, что мы пытаемся передать значение 19 как месяц, а не как день. Мы можем решить эту проблему, подставив значение, которое больше соответствует ожиданиям американцев:

SET LANGUAGE us_english;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME);

Теперь наш SELECT сработает отлично. Если мы изменим язык обратно на british и подставим то же значение, мы снова получим ошибку «out-of-range».

Конечно, мы могли бы изменить язык еще раз, но это не очень эффективное, если мы разрабатываем интернациональное приложение. Лучше будет, если наши значения даты/времени будут универсальными по своей природе, например, используя формат «год-месяц-день«. Например, предположим, что мы пытаемся передать значение даты/времени в данном формате с дефисами:

SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME);

Этот формат считается более универсальным способом передачи данных времени и даты. В конце концов, именно в таком виде SQL Server возвращает данные. Для разделения компонент даты можно использовать тире, косую черту или точку, до тех пор, пока значения соответствуют структуре «год-месяц-день«. Однако, несмотря на универсальность формата, оператор SELECT снова возвращает ошибку:

Msg 242, Level 16, State 3, Line 204
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 204
Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

Но посмотрите, что произойдет, если мы будем придерживаться того же языка и того же формата, но на этот раз преобразуем данные в DATETIME2:

SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME2);

Теперь SELECT без проблем преобразует дату и возвращает следующий результат:

2015-05-19 10:11:12.0000000

Оказывается, что формат «год-месяц-день» все еще зависит от настроек SQL Server, когда речь идет о типе данных DATETIME, но не DATETIME2.

Если мы придерживаемся типа данных DATETIME2, то можем избежать языковой проблемы при использовании формата «год-месяц-день«, который является лучшим вариантом, при работе с SQL Server 2008 или более поздним. Но не у всех есть такая роскошь. Нам нужен формат, который будет независимым от типа и языка. По этой причине многие разработчики по умолчанию используют такой формат, как ISO 8601:

SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19T10:11:12';
SELECT CAST(@a AS DATETIME);

На этот раз выражение выполняется без ошибок. Используя универсальный формат для значения времени/даты, мы можем лучше гарантировать, что мы получим желаемые результаты, независимо от того где мы находимся — в Сиэтле, Кембридже или Риме.

Ошибка #3: Снова забыть о людях, которые живут в других частях света

Форматы даты — не единственное, где мы можем попасть в неприятности, когда разрабатываем приложение, которое охватывает несколько географических регионов. Также могут возникнуть проблемы с часовыми поясами, если потребуется отслеживать данные за периоды времени по нескольким регионам.

Одна из проблем заключается в том, что большинство типов даты/времени в SQL Server довольно неоднозначны. Например, у нас есть таблица, которая отслеживает события, связанные с безопасностью и одна из строк показывает событие, произошедшее 15 мая 2015 года в 3:30 утра. Это время на локальной машине? Или это время сервера? Настроен ли SQL Server, чтобы использовать время, отличное от местного? Является ли это значением в UTC? Без какого-либо механизма, обеспечивающего контекст, это значение почти бессмысленно.

Во многих случаях это не проблема, но для систем мониторинга или для критических процессов, точное время имеет важное значение. Представьте себе, если был взломан международный банк, деньги украдены со счетов, а у следователей нет возможности узнать, когда точно произошло это событие. А как насчет аналитики с учетом времени? Анализ исторических данных может стать сложным, если данные охватывают часовые пояса и нет механизма обеспечения контекста, такого как часовой пояс, в котором были введены данные.

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

К счастью, в SQL Server 2008 появился тип данных DATETIMEOFFSET, который должен сделать управление датой/временем немного проще. Этот тип хранит данные аналогично DATETIME2 с дополнительной парой байт, используемой для часового пояса (относительно UTC).

Рассмотрим следующий пример:

DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT @pdt;

Системная функция SYSDATETIMEOFFSET возвращает текущую дату и время в виде DATETIMEOFFSET, что означает, что оно включает в себя дату, время и значение UTC-смещения:

2015-05-08 17:57:23.5350000 -07:00

В этом случае значение даты/времени отстает от UTC на семь часов, и мы окажемся на западном побережье США. Для получения только значения смещения, то можем использовать функцию DATENAME:

DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT DATENAME(tzoffset, @pdt);

Как и ожидалось, SELECT возвращает только разницу с UTC:

-07:00

Далее мы можем продемонстрировать, как работает тип данных DATETIMEOFFSET сравнивая его с UTC-аналогом:

DECLARE 
  @utc DATETIMEOFFSET = SYSUTCDATETIME(),
  @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT @utc AS UTC, @pdt AS PDT;

Как показывают следующие результаты, дата и время UTC на семь часов опережают Тихоокеанскую (PDT) дату и время.

В SQL Server 2008 была добавлена системная функция SWITCHOFFSET для изменения значения DATETIMEOFFSET на другой часовой пояс:

DECLARE @a DATETIMEOFFSET = '2015-05-08 17:57:53.3390000 -07:00';
SELECT SWITCHOFFSET(@a, '-05:00');

В данном случае мы просто изменяем значение UTC-смещения с -07:00 на -05:00 при получении данных:

2015-05-08 19:57:53.3390000 -05:00

Очевидно, что SQL Server значительно облегчил работу с часовыми поясами, и нет причин не пользоваться этими возможностями, если вы используете SQL Server 2008 и старше. Однако существует проблема, которую SQL Server не смог решить — переход на летнее и зимнее время.

Давайте посмотрим, что происходит, когда мы сравниваем часовые пояса между Мельбурном и Сиэтлом на 1 апреля 2015 года:

DECLARE 
  @mel DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 +11:00',
  @sea DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 -07:00';
SELECT DATEDIFF(hh, @mel, @sea);

Результат:

18

На данный момент и Мельбурн, и Сиэтл находятся на летнем времени, давая нам 18-часовую разницу между датами. Однако давайте сравним 1 мая 2015 года, используя те же значения смещения UTC:

DECLARE 
  @mel DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 +11:00',
  @sea DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 -07:00';
SELECT DATEDIFF(hh, @mel, @sea);

Результат:

18

Мы снова видим разницу в 18 часов, хотя на самом деле должно быть 17, потому что Мельбурн вернулся к зимнему времени 5 апреля. Значение смещения, которое мы должны были использовать 5-го мая для Мельбурна, составляет +10:00.

Несмотря на то, что это очень простой пример, он показывает на большую проблему, связанную с отслеживанием летнего и зимнего времени по всему миру. Не во всех регионах происходит смена летнего и зимнего времени. А там, где происходит, то она не всегда бывает в одно и то же время. Кроме того, даты начала и окончания меняются от года к году. И эти даты становятся еще менее предсказуемыми из-за того, что изменяются законы, определяющие переход на летнее/зимнее время.

Добавьте к этому еще тот факт, что сами часовые пояса могут сильно различаться даже в пределах региона или страны. Например, штат Аризона в США. Большая часть штата не меняет время. Это означает, что часть года они синхронизированы с Колорадо, а остальную часть года живут как в Калифорнии.

Все это указывает на то, что вы не можете полагаться только на DATETIMEOFFSET для обеспечения надежности ваших значений даты/времени. Изменение часовых поясов и неуправляемый переход на летнее/зимнее время делают работу с часовыми поясами сложной задачей без наличия простого решения. Если ваши приложения и аналитика полагаются на точное измерение даты и времени, то приложение должно содержать логику, необходимую для обработки таких ситуаций.

Ошибка #4: Относиться к DATETIME2 только как к более точному DATETIME

Несмотря на то, что DATETIME2 появился еще в SQL Server 2008, многие разработчики не спешат его использовать и используют DATETIME по привычке, а не по каким-то другим причинам. Но, помимо большей точности, DATETIME2 имеет еще другие преимущества перед DATETIME.

Давайте начнем с того, что посмотрим на них в действии:

DECLARE 
  @a DATETIME2 = '2015-05-12 09:47:12.5556789',
  @b DATETIME  = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

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

Первое, на что стоит обратить внимание, это округление SQL Server’ом времени в DATETIME до ближайшей .003 секунды, т.е. значения хранятся с приращением .000, .003 или 0,007 секунды. (Прим. переводчика — на самом деле шаг 1/300 сек = 0,0033333333333333… сек, но из-за округлений получается .000, .003, .007, .010, .013, …)

В этом отношении DATETIME2 является гораздо более точным. Хотя значение и обрезается при превышении семи знаков после запятой, но округления не происходит, если значение находится в допустимых пределах. Например, .555678999 округляется до .5556790, но значение, как .9999999, не округляется.

Так что в этом отношении DATETIME2 также более точен, чем DATETIME. Кроме того, в отличие от DATETIME, вы можете контролировать точность DATETIME2. Например, в следующем примере установим точность времени DATETIME2 в 3:

DECLARE 
  @a DATETIME2(3) = '2015-05-12 09:47:12.5556789',
  @b DATETIME     = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

Как видно, значение DATETIME2 теперь включает только три десятичных разряда, так же как и DATETIME.

Еще раз, часть времени DATETIME2 округляется, потому что представленное значение превысило указанную точность, но даже это округление является более точным, чем то, которое мы получаем с DATETIME. Хотя оба значения занимают три знака после запятой, SQL Server использует 7 байт для хранения DATETIME2 и 8 байт для DATETIME.

Фактически DATETIME2 использует 8 байт при точности больше 4, и только 6 байт, если точность меньше 3. C DATETIME2 вы можете получить не только большую точность, но и сэкономить место, что может быть важным работе с большими объемами данных. Тип DATETIME2 также позволяет полностью удалить знаки после запятой:

DECLARE 
  @a DATETIME2(0) = '2015-05-12 09:47:12.5556789',
  @b DATETIME     = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

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

Еще один важный момент при сравнении DATETIME2 и DATETIME это то, что DATETIME2 поддерживает гораздо более широкий диапазон дат (с 1 января 0001). В то время как DATETIME только с 1 января 1753 года. (Оба они ограничены 9999 годом, что, я уверен, многих успокоит).

Стоит также сказать пару слов и о типах DATE и TIME, которые предоставляют такую же точность.

DECLARE 
  @a DATE = '2015-05-12 09:47:12.5556789',
  @b TIME = '2015-05-12 09:47:12.5556789';
SELECT @a AS DateType, @b AS TimeType;

Как вы можете видеть, типы DATE и TIME являются хорошим дополнением для удобной работы с датой/временем:

Конечно, в старые приложения и системы не всегда просто внедрить новые типы данных, но при создании новых систем нет причин не использовать эти типы (если только вы не работаете с версиями SQL Server меньше 2008 или с технологиями, которые не могут обрабатывать значения DATETIME2).

У типа DATETIME2 и других новых типов есть много преимуществ, чтобы их игнорировать, в том числе лучшее соответствие типам даты/времени .NET. И как было отмечено ранее, тип DATETIME2 более снисходителен к форматам даты/времени, которые вы передаете в базу данных. Очевидно, что пришло время избавиться от старых привычек и отказаться от DATETIME.

Ошибка #5: Игнорировать округление даты/времени

В предыдущем разделе мы затронули вопрос округления, но эта тема заслуживает большего внимания. Особенно в том, что касается DATETIME и SMALLDATETIME. Сначала давайте посмотрим, что произойдет, если мы округлим DATETIME2:

DECLARE 
  @a DATETIME2 = '2015-05-12 23:32:12.1234567',
  @b DATETIME2 = '2015-05-12 23:32:12.123456789',
  @c DATETIME2 = '2015-05-12 23:59:59.999999999';
SELECT '2015-05-12 23:32:12.1234567' AS OrigValue, @a AS StoredValue
UNION ALL
SELECT '2015-05-12 23:32:12.123456789', @b
UNION ALL
SELECT '2015-05-12 23:59:59.999999999', @c;

Здесь точность DATETIME2 по умолчанию равна 7 — это количество знаков после запятой для секунд. Как видно из результатов, значение @a никак не округляется, а @b и @c округляются:

Значение @b округляется, как мы и ожидали: девять цифр сокращаются до семи цифр, а значение 123456789 округляется до 1234568. Значение @c также подчиняется аналогичной логике. Однако, так как мы округляем вверх, то переходим к следующему дню. В обоих случаях SQL Server работает вполне предсказуемо. Хотя существует вероятность того, что значение будет увеличено до следующего дня, но оно все равно соответствует ожидаемому поведению.

Теперь давайте посмотрим, что происходит со значениями DATETIME:

DECLARE 
  @a DATETIME = '2015-05-12 23:59:59.996',
  @b DATETIME = '2015-05-12 23:59:59.998',
  @c DATETIME = '2015-05-12 23:59:59.999';
SELECT '2015-05-12 23:59:59.996' AS OrigValue, @a AS DatetimeValue
UNION ALL
SELECT '2015-05-12 23:59:59.998', @b
UNION ALL
SELECT '2015-05-12 23:59:59.999', @c;

Значение @a округляется в большую сторону, @b округляется в меньшую сторону, а @c переносится на следующий день:

Что удивительно в этом округлении, так это то, что значения, которые мы передаем, не превышают точности DATETIME, но округление все равно происходит. Как говорилось ранее, SQL Server хранит данные в DATETIME с шагом .000, .003 и .007 секунд. Это может стать проблемой с аналитическими отчетами, требующих высокой точности. И это еще более проблематично, когда мы не можем присвоить точное значение, потому существует вероятность того, что оно будет округлено до следующего дня.

Вероятность потерять день может показаться незначительной, но это может произойти неожиданным образом. Например, предположим, что мы хотим преобразовать значение DATETIME2 в значение DATETIME:

DECLARE @a DATETIME2 = '2015-05-12 23:59:59.9986789';
DECLARE @b DATETIME  = @a;
SELECT @a AS Datetime2Value, @b AS DatetimeValue;

Поскольку исходная точность превышает то, с чем DATETIME может справиться, то происходит округление с переходом на следующий день:

Но мы можем столкнуться с еще более запутанными проблемами SMALLDATETIME:

DECLARE 
  @a SMALLDATETIME = '2015-05-12 23:22:22',
  @b SMALLDATETIME = '2015-05-12 23:22:30',
  @c SMALLDATETIME = '2015-05-12 23:22:52',
  @d SMALLDATETIME = '2015-05-12 23:59:52';
SELECT '2015-05-12 23:22:22' AS OrigValue, @a AS SmalldatetimeValue
UNION ALL
SELECT '2015-05-12 23:22:30', @b
UNION ALL
SELECT '2015-05-12 23:22:52', @c
UNION ALL
SELECT '2015-05-12 23:59:52', @d;

Несмотря на то, что возвращаемые значения всегда показывают «00» секунд, точность у SMALLDATETIME только до ближайшей минуты,

Округление значения @a довольно просто: эти 22 секунды округляются в меньшую сторону, поэтому значение минуты остается неизменным. Значение @b округляется в большую сторону, так как SQL Server округляет 30 секунд и более до следующей минуты. Это также относится и к значению @c. Однако, значение @d переходит на следующий день, потому что 59 минут также округляются в большую сторону, что привело к тому, что 23 часа перешли на следующий день.

Теперь посмотрим, что произойдет, если мы добавим доли секунды:

DECLARE @a SMALLDATETIME = '2015-05-12 23:59:29.999';
SELECT @a;

И снова мы переходим на следующий день:

2015-05-13 00:00:00

Вы должны быть осторожны, когда речь идет о типах DATETIME и SMALLDATIME, так как можете получить данные, которые будут неожиданным образом влиять на ваши результаты. Если есть возможность использовать DATETIME2, то используйте его.

Ошибка #6: Делать лишнюю работу для удаления времени из полной даты

Часто вас интересует только день без времени и вы хотите обнулить время или совсем избавиться от него. До SQL Server 2008 приходилось немного потрудиться, чтобы получить только дату, но теперь есть тип DATE, который делает нашу жизнь проще:

DECLARE @a DATETIME2(3) = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS DATE);

Здесь мы просто преобразуем значение DATETIME2 в значение DATE, и все работает отлично:

2015-05-12

Мы можем также легко преобразовать значение DATETIME в значение DATE и получить такие же результаты:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS DATE);

Также можем преобразовать наше исходное значение в тип TIME:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS TIME(3));

Как и ожидалось, SELECT теперь возвращает только время:

22:14:18.003

Прежде чем мы продолжим, обязательно обратите внимание на типы данных DATE и TIME. Если в вашей конкретной ситуации они вам доступны, то используйте их и забудьте об остальной части этого раздела. Эти типы являются лучшими способами для получения даты из значений даты со временем.

Однако если у вас нет такой возможности, тогда вы должны искать другие решения. Один из вариантов заключается в следующем:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(CONVERT(CHAR(8), @a, 112) AS DATETIME);

Как видите, мы преобразовываем дату сначала в строку, используя формат ISO (112), а затем обратно в DATETIME, и получаем следующий результат:

2015-05-12 00:00:00.000

Хотя это решение работает, но оно не сделает SQL Server счастливым. Для одной или двух строк это не проблема, но представьте, если вы конвертируете миллионы строк.

Решением получше будет использование системных функций DATEADD и DATEDIFF для обнуления этих дат:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a), 0);

Здесь мы вычисляем количество дней между нулевым днем (1 января 1900 г.) и нашей датой. А затем добавляем разницу к нулевому дню, получая следующий результат:

2015-05-12 00:00:00.000

В этом случае SQL Server будет счастлив, потому что он сможет воспользоваться целочисленной природой DATETIME.

Аналогичный подход заключается в том, чтобы вместо нулевого дня указать какую-то другую дату:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(DAY, DATEDIFF(DAY, '20010101', @a), '20010101');

Здесь мы также получаем нужный нам результат, вычисляя разницу между 1 января 2001 г. и указанной датой. Самое интересное в этом подходе то, что мы можем использовать его немного для других целей. Например, мы можем получить первый день месяца для указанной даты:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20010101');

Все, что мы сделали, это изменили аргумент DAY на MONTH. И получили следующий результат:

2015-05-01 00:00:00.000

Если мы изменим дату, указанную в функции DATEADD, на один день раньше нашей базовой даты, мы можем получить последний день предыдущего месяца:

DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20001231');

Теперь SELECT возвращает следующие результаты:

2015-04-30 00:00:00.000

Даже если вам доступны типы DATE и TIME, то эти два последних примера могут быть удобными для получения необходимых вам данных.

Ошибка #7: Не понимать как работает функция DATEDIFF

Раз уж мы заговорили о функции DATEDIFF, то стоит присмотреться к ней повнимательнее. Если мы не будем осторожны при ее использовании, то можем получить странные результаты. Например, пытаемся определить количество минут между двумя значениями даты/времени:

DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(mi, @a, @b);

В этом случае SELECT возвращает значение 1, то есть целую минуту, несмотря на то, что между этими датами разница всего в одну секунду.

То же самое произойдет, если мы попытаемся найти разницу в часах:

DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(hh, @a, @b);

На этот раз SELECT показывает, что разница между значениями составляет один час, а не одну секунду.

То же самое происходит с месяцами:

DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(mm, @a, @b);

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

Проблема не в самой функции DATEDIFF, а, скорее, в нашем понимании того, как она работает. SQL Server смотрит не дальше той части даты (будь то год, месяц, час или минута), которую мы указали. Поэтому если указать месяц, то сравниваются годы и месяцы, но не более. Значения могут отличаться всего на одну секунду, но SQL Server заботят только годы и месяцы.

Один из способов это обойти — опуститься на один уровень глубже, чем нам нужно. Например, мы хотим получить количество минут между двумя значениями. Вместо этого мы можем получить количество секунд, а затем разделить на 60:

DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(ss, @a, @b)/60.0000;

Теперь SELECT возвращает 0.0166666, а не 1, что гораздо ближе к истине.

Ошибка #8: Небрежно относиться к условиям поиска

Использование даты и времени в условиях поиска — дело тонкое и надо быть осторожным. Не проявляя должной внимательности, вы можете получить неправильные данные или, что еще хуже, что-то изменить или удалить по ошибке.

Чтобы продемонстрировать, почему это может быть проблемой, давайте создадим временную таблицу и заполним ее несколькими строками, которые содержат DATETIME2:

CREATE TABLE #a (ColA INT, ColB DATETIME2(3));
INSERT INTO #a VALUES
  (101, '2015-05-06 22:43:55.123'),
  (102, '2015-05-06 23:59:59.997'),
  (103, '2015-05-07 00:00:00.000'),
  (104, '2015-05-07 17:33:36.321'),
  (105, '2015-05-08 00:00:00.000'),
  (106, '2015-05-08 10:18:12.987');

Теперь попробуем выбрать строки на 7 мая 2015 года:

SELECT ColA, ColB FROM #a
WHERE ColB = '2015-05-07';

Как мы видим, наш запрос возвращает только одну строку, когда мы хотим видеть две:

Проблема с получением данных таким образом заключается в том, что тип данных DATETIME2, как и другие типы данных даты/времени, хранят и дату и время. Причем время часто отличается от полуночи (это когда все нули). Однако, когда мы сравниваем со значением, в котором хранится только дата без времени, то SQL Server использует полночь (00:00:00) для времени.

В результате, сравнение, подобное приведенному выше, отбрасывает все строки, кроме тех, которые соответствуют указанной дате и полуночи для времени.

Один из обходных путей — это преобразовать данные из таблицы в тип, соответствующий только дате:

SELECT ColA, ColB FROM #a
WHERE CONVERT(CHAR(8), ColB, 112) = '20150507';

Теперь SELECT возвращает результаты, которые мы хотим:

Проблема такого подхода заключается в том, что, как упоминалось ранее, SQL Server не любит таких преобразований. SQL Server не может ни эффективно использовать индексы, ни извлечь выгоду от целочисленной природы типов даты/времени.

Из-за этих проблем иногда обращаются к оператору BETWEEN:

SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-06' AND '2015-05-08';

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

Проблема здесь в том, что оператор BETWEEN при поиске включает начальную и конечную даты. Давайте попробуем указать только дату, которая нас интересует:

SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07';

И снова SELECT возвращает только одну строку:

На этот раз проблема похожа на использование WHERE ColB = '2015-05-07'. Оператор WHERE обрабатывает условие на основе полного значения даты со временем, поэтому наше условие WHERE, на самом деле, выглядит следующим образом:

WHERE ColB BETWEEN '2015-05-07 00:00:00.000' AND '2015-05-07 00:00:00.000';

Конечно, это условие соответствует только одной строке. Однако, мы можем решить эту проблему, если добавим время к конечной дате.

SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59.999';

Теперь оператор SELECT возвращает результат, который мы хотим получить:

Хотя этот подход отлично работает для DATETIME2, но мы можем упростить код, используя в условии операторы сравнения:

SELECT ColA, ColB FROM #a
WHERE ColB >= '2015-05-07' AND colB < '2015-05-08';

Оператор SELECT снова возвращает нужные нам результаты, сохраняя при этом наш запрос простым. Такой подход также является лучшим способом работы с типом DATETIME.

Предположим, что мы изначально определили ColB как DATETIME и заполнили таблицу:

CREATE TABLE #a (ColA INT, ColB DATETIME);
INSERT INTO #a VALUES
  (101, '2015-05-06 22:43:55.123'),
  (102, '2015-05-06 23:59:59.997'),
  (103, '2015-05-07 00:00:00.000'),
  (104, '2015-05-07 17:33:36.321'),
  (105, '2015-05-08 00:00:00.000'),
  (106, '2015-05-08 10:18:12.987');

Теперь давайте проверим BETWEEN с указанием точного времени:

SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59.999';

SELECT возвращает три строки:

Такой результат получается, потому что мы сравниваем даты в BETWEEN со значением DATETIME в таблице. SQL Server округляет значения, которые мы указали в условиях поиска, чтобы соответствовать точности DATETIME. Это приводит к тому, что наша конечная дата переносится на следующий день, и в выборку попадает строка, соответствующая этому дню. Чтобы обойти это, мы можем снова обратиться к операторам сравнения:

SELECT ColA, ColB FROM #a
WHERE ColB >= '2015-05-07' AND colB < '2015-05-08';

Теперь, как мы и ожидаем, возвращается две строки:

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

Ошибка #9: Забыть о диапазонах в типах данных для даты/времени

Это может показаться очевидным, но об этом все-таки стоит сказать. Если вы конвертируете данные из одного типа даты/времени в другой, то вы должны помнить о допустимых диапазонах дат.

Посмотрите пример:

DECLARE 
  @a DATETIME2 = '1623-01-01',
  @b DATETIME  = NULL;
SET @b = @a;
SELECT @b;

Мы пытаемся преобразовать значение DATETIME2 с 1623-годом в значение DATETIME. К сожалению, тип данных DATETIME поддерживает только годы с 1753 по 9999. Хотя это хорошо для тех, кто смотрит в будущее. Но не для тех, кто увлекается историей или хочет выполнять запросы, аналогичные приведенному выше, который приводит к результатам, подобным следующему:

Msg 242, Level 16, State 3, Line 792
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 792
Преобразование типа данных datetime2 в тип данных datetime привело к выходу значения за пределы диапазона.

Сообщение довольно очевидно. Тип данных DATETIME не имеет никакого отношения к 1623 году.

Тип данных SMALLDATETIME еще более ограничен:

DECLARE 
  @a DATETIME      = '1823-01-01',
  @b SMALLDATETIME = NULL;
SET @b = @a;
SELECT @b;

Оператор SELECT снова вернет ошибку «out-of-range», потому что тип данных SMALLDATETIME поддерживает только годы с 1900 по 2079. При преобразовании данных из одного типа в другой обязательно учитывайте эти ограничения.

Ошибка #10: Не использовать преимуществ функций работы с датой и временем

В SQL Server 2008 добавлены отличные встроенные функции для работы с датой и временем, и было бы стыдно не воспользоваться ими в полной мере. Некоторые, однако, откроют для себя новый мир за пределами GETDATE или GETUTCDATE.

Давайте посмотрим на некоторые функции даты и времени в действии:

SELECT GETDATE();
SELECT SYSDATETIME();
SELECT GETUTCDATE();
SELECT SYSUTCDATETIME();
SELECT SYSDATETIMEOFFSET();

Как видно из результатов, у нас есть множество вариантов, из которых можно выбрать:

2015-05-14 10:28:21.700
2015-05-14 10:28:21.7030000
2015-05-14 17:28:21.700
2015-05-14 17:28:21.7030000
2015-05-14 10:28:21.7030000 -07:00

Функция SYSDATETIME возвращает текущую дату и время в виде DATETIME2. Функция SYSUTCDATETIME возвращает те же данные, но в виде UTC-значения. Функция SYSDATIMEOFFSET возвращает текущую дату и время как DATETIMEOFFSET, что означает также получение UTC-смещения.

В SQL Server 2008 также улучшены функции DATENAME и DATEPART для поддержки новых типов даты/времени. Теперь они включают в себя параметры для части даты: микросекунды, наносекунды и UTC-смещение. В следующем примере показаны эти возможности, используемые в функции DATENAME:

DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';
SELECT 
  DATENAME(mcs, @a) AS Microseconds,
  DATENAME(ns, @a) AS Nanoseconds,
  DATENAME(tz, @a) AS TimezoneOffset;

Результат:

Функция DATEPART работает практически так же:

DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';
SELECT 
  DATEPART(mcs, @a) AS Microseconds,
  DATEPART(ns, @a) AS Nanoseconds,
  DATEPART(tz, @a) AS TimezoneOffset;

Результат:

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

Вы должны быть знакомы со всеми функциями даты/времени, потому что они могут повысить вашу эффективность при работе с датой/временем, особенно если вы используете новые типы даты/времени.

На этом все. Ждем ваши комментарии и приглашаем на бесплатный вебинар на тему: «Parameter sniffing в SQL Server: что это и почему возникает».

Сообщения без ответов | Активные темы