Путаница в T-SQL между датами

Я работаю с T-SQL в SQL Server 2000, и у меня есть таблица TRANSACTIONS, в которой есть столбец даты TRANDATE, определенный как DateTime, среди многих других столбцов, которые не имеют отношения к этому вопросу.

Таблица заполнена транзакциями за многие годы. Я столкнулся с кодом, тестом, который меня смутил. Есть простой SELECT, например:

SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010' and '12/31/2010' ORDER BY TRANDATE

и он не возвращает две строки данных, которые, как я знаю, находятся в этой таблице.

С приведенным выше оператором последняя возвращаемая строка по порядку имеет TRANDATE из: 2010-12-31 00:00:00.000

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

SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010 00:00:00' and '12/31/2010 23:59:59' ORDER BY TRANDATE

Я попытался выяснить, почему оператор BETWEEN не включает ВСЕ строки за 24 периода 31.12.2010 при использовании первого SELECT выше. И почему к оператору SELECT нужно добавлять явные часы, как во втором, модифицированном, операторе, чтобы заставить его извлекать правильное количество строк?

Это из-за того, что TRANDATE определяется как "DATETIME"?

Основываясь на этом выводе, я думаю, что мне придется просмотреть весь этот старый код, потому что эти операторы BETWEEN разбросаны по всей этой старой системе, и кажется, что она не извлекает все данные должным образом. Я просто хотел сначала разъяснений от некоторых людей. Спасибо!


between tsql sql-server-2000
person ONDEV    schedule 25.03.2011    source источник
comment
'12/31/2010 23:59:59' по-прежнему будет пропускать время между 12/31/2010 23:59:59 и 12/31/2010 23:59:59:997. Не используйте between для даты и времени, используйте вместо этого >= and <.   -  person Martin Smith    schedule 25.03.2011
comment
@Мартин: Спасибо. И спасибо всем остальным. Особенно @GSerg, который в основном ответил на мой вопрос ниже. Кажется, я не могу проголосовать, пока у меня не будет 15 баллов, поэтому вы все ничего не видите (от меня). Но теперь я понимаю, исходя из ответов каждого. Спасибо. У меня есть работа! :-)   -  person ONDEV    schedule 25.03.2011

Ответы (5)


arrow_upward
14
arrow_downward

Дата — это момент времени, а не промежуток времени.

'12/31/2010' тоже точка. А именно, сейчас полночь 31 декабря.
Все, что произошло после этой точки, игнорируется.
Это именно то поведение, которое вам нужно (даже если вы еще этого не осознали).

Не думайте, что когда вы решите опустить часть времени, она волшебным образом будет считаться "any". Будет "all zeroes", то есть полночь.

Если вы хотите включить в свой запрос весь день, не указывая 23:59:59 (что, кстати, исключает последнюю секунду дня между моментом 23:59:59 текущего дня и моментом 00:00:00 следующего дня), вы можете сделать либо с помощью строгих неравенств (>, <), ограниченных первыми точками времени, которые вам не нужны:

WHERE TRANDATE >='12/01/2010 00:00:00' and TRANDATE < '01/01/2011'

или путем сравнения значений дат, приведенных к DATE:

WHERE CAST(TRANDATE AS DATE) between '12/01/2010' and '12/31/2010'

(можно поместить этот тип приведения в предложение WHERE, это можно обсуждать).

person GSerg    schedule 25.03.2011

arrow_upward
4
arrow_downward

Как вы уже обнаружили, если вы не укажете время при вводе даты, по умолчанию будет полночь утром в день даты. Итак, 31.12.2010 заканчивается в полночь, когда начинается этот день.

Чтобы получить все даты на 31 декабря 2010 г., вы можете либо указать время, как вы сделали, или добавить один день к дате окончания. Без указания времени 01.01.2011 заканчивается ровно в полночь 31.12.2010. Итак, вы можете сделать BETWEEN 12/1/2010 AND 1/1/2011. Вы можете использовать DATEADD, чтобы добавить день в свой SQL, если это упростит задачу.

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

Вот один из способов выполнить DATEADD:

DECLARE @FromDate datetime, @ToDate datetime
// These might be stored procedure input parameters
SET @FromDate = '12/1/2010'
SET @ToDate = '12/31/2010'

SET @ToDate = DATEADD(d, 1, @ToDate)

Затем вы используете @ToDate в предложении WHERE во фразе BETWEEN обычным способом.

person DOK    schedule 25.03.2011

arrow_upward
3
arrow_downward

«01.12.2010» означает «01.12.2010 00:00:00», а «31.12.2010» означает «31.12.2010 00:00:00». Вот почему значения даты и времени, выпадающие на более поздний день 31 декабря 2010 г., исключаются из результатов вашего запроса.

person Greg    schedule 25.03.2011

arrow_upward
2
arrow_downward

Каков будет ваш ожидаемый результат, если я сделаю это

Insert "12/31/2010" into your datetime column?

Точно: 31-12-2010 00:00:00

Так почему же вы ожидаете, что он будет другим в качестве аргумента для запроса?

person Pleun    schedule 25.03.2011

arrow_upward
1
arrow_downward

Вы вроде уже сами ответили на свой вопрос. То, что вы заметили, это то, как работает SQL Server.

Если вам нужно подтверждение, в этом документе MSDN есть следующее, чтобы сказать о Это

Если часть времени не указана, по умолчанию используется 00:00. Обратите внимание, что строка, содержащая часть времени после 00:00, on 1998-0105 не будет возвращено этим запросом, так как оно выходит за пределы диапазона.

Изменить

Что касается вашего комментария, datetime по существу является значением с плавающей запятой.

Следующий сценарий показывает, с какими числами работает SQL Server.
40541.9749 (31.12.2010 23:23:59) не может быть включен, если ваша верхняя граница равна 40541 (31.12.2010).

DECLARE @ADateTime1 DATETIME
DECLARE @ADateTime2 DATETIME
DECLARE @ADateTime1AsFloat FLOAT
DECLARE @ADateTime2AsFloat FLOAT

SET @ADateTime1 = '12/31/2010'
SET @ADateTime2 = '12/31/2010 23:23:59'

SET @ADateTime1AsFloat = CAST(@ADateTime1 AS FLOAT)
SET @ADateTime2AsFloat = CAST(@ADateTime2 AS FLOAT)

SELECT @ADateTime1AsFloat, @ADateTime2AsFloat
person Lieven Keersmaekers    schedule 25.03.2011
comment
Хорошо, но это мое замешательство. Вы правы, я, кажется, «понимаю», ЧТО он делает, но почему я сбит с толку... 31.12.2010 имеет 24 часа, и если у меня есть ряд данных с TRANDATE 2010-12 -31 18:12:54.000, почему МЕЖДУ (без часов) это не подхватывает? 31.12.2010 остается 31.12.2010 независимо от того, 00:00:00 или 18:12:54 ИЛИ 23:59:59! - person ONDEV; 25.03.2011