Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона

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

SELECT COUNT(*)
FROM dbo.profile 
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' 
AND '2014-11-30 23:59:59.997';

SMSS возвращает:

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

Я не понимаю, почему данные преобразуются из varchar в datetime, когда 'Created' установлен в datetime:

Нужно ли мне сказать серверу, что 'Created' имеет значение datetime? Если нет, то почему я получаю это сообщение varchar?

Редактирование: Значение в базе данных было YYYY-MM-DD. Ответ от @SqlZim ниже говорит, что мне нужно использовать convert(), чтобы сообщить sql, в каком формате дата находится в базе данных - и заменить символ пробела на букву T:

select count(*) 
from dbo.profile 
where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
and convert(datetime,'2014-11-30T23:59:59.997');`
Paul White says GoFundMonica
Редактировал вопрос 17-го декабря 2014 в 12:48
Администрация баз данных
datetime sql-server type-conversion
16-го декабря 2014 в 4:21
40 просмотров
Решение

Я проверил ваш профиль и увидел, что вы находитесь в Великобритании. Если ваш sql-сервер настроен на использование формата даты dmy, то это объясняет вашу проблему. Без использования символа 'T' вместо пробела в строке даты Sql Server не распознает ее как формат ISO8601.

Попробуйте сделать следующее:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

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

редактировать: чтобы уточнить, что значение вне диапазона в вашем сообщении об ошибке было интерпретацией месяца как 30, а дня как 11.

SqlZim
Редактировал ответ 16-го декабря 2014 в 5:27
Комментарии (0)

Я не понимаю, почему данные преобразуются из varchar в datetime, когда 'Created' установлен в datetime.

Литералы, которые вы предоставляете для сравнения со столбцом Created, являются строками. Чтобы сравнить эти литералы со столбцом datetime, SQL Server пытается преобразовать строки в типы datetime в соответствии с правилами приоритета типов данных. Без явной информации о формате строк SQL Server следует своим запутанным правилам для интерпретации строк как времени даты.

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

В вопросе используются строки в каноническом формате ODBC (с миллисекундами) (стиль 121). Явное определение типа данных и стиля строки приводит к следующему:

SELECT COUNT(*)
FROM dbo.profile 
WHERE [Created] BETWEEN 
    CONVERT(datetime, '2014-11-01 00:00:00.000', 121)
    AND 
    CONVERT(datetime, '2014-11-30 23:59:59.997', 121);

Тем не менее, существуют веские причины (как указывает Аарон в своем ответе) для использования полуоткрытого диапазона вместо BETWEEN (я использую стиль 120 ниже просто для разнообразия):

SELECT COUNT(*)
FROM dbo.profile 
WHERE
    [Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
    AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);

Явное определение типов - очень хорошая привычка, особенно при работе с датами и временем.

Community
Редактировал ответ 13-го апреля 2017 в 12:42
Комментарии (0)

Поскольку [BETWEEN очень проблематичен из-за округления различных типов даты/времени и других проблем] (http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx), и поскольку [YYY-MM-DD не является безопасным форматом без неудобного T] (http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx), открытый диапазон с использованием стандарта ISO полные даты без разделителей является гораздо лучшим подходом:

WHERE Created >= '20141101' AND Created < '20141201';
Aaron Bertrand
Редактировал ответ 16-го декабря 2014 в 5:57
Комментарии (0)