Я пытаюсь выполнить простой запрос, чтобы получить все строки, созданные в ноябре:
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');`
Я проверил ваш профиль и увидел, что вы находитесь в Великобритании. Если ваш 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.
Я не понимаю, почему данные преобразуются из 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);
Явное определение типов - очень хорошая привычка, особенно при работе с датами и временем.
Поскольку [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';