Ограничение диапазона дат с точностью в MS SQL / SQL Server 2005
Я хочу ограничить отчет, чтобы он возвращал записи от даты A до даты B. Это то, что я делал:
declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'
-- test what are the start and end dates
select min(date),max(date) from view_Inspections
where date between @startDate and @endDate
... который, как мне сказали, возвращал записи с 12 часов утра 1 января до 23:59 31 марта (эта полночь используется по умолчанию, когда время не указано). Но я заметил несоответствие: если запись имеет время 00:00:00, она будет частью этого набора.
Есть ли более точный способ сделать это, чтобы он возвращал именно тот диапазон дат, который мне нужен? *
Я пробовал использовать время:
declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008 00:00:00'
set @endDate = '04/01/2008 11:59:59'
-- test what are the start and end dates
select min(date),max(date) from view_Inspections
where date between @startDate and @endDate
... но я заметил кое-что странное: SQL Server ЗАКРУГЛЕТ на сотую секунду вдвое больше. Так что я получаю ту запись 1 апреля (ха! Первоапрельская запись! Grr), если использую любое время позже 11:59:29. Это почему?
- (Я уверен, что есть. Я новичок в этом. Спасибо за вашу помощь!)
Ответы 5
Всегда есть легкий вариант:
declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'
-- test what are the start and end dates
select min(date),max(date) from view_Inspections
where date >= @startDate
and date < @endDate
Я предлагаю вам объяснить, что BETWEEN / AND является включительным, а требуемый диапазон поиска является полуоткрытым (или полузакрытым) и должен исключать конечную точку.
Я подозреваю, что столбец даты в view_Inspections является типом данных SmallDateTime. Этот тип данных имеет точность до 1 минуты, что объясняет ваши неожиданные результаты (округление секунд до ближайшей минуты).
Метод, который предлагает Роланд Шоу, - лучший способ изменить ваш запрос в соответствии с вашими требованиями.
Оператор BETWEEN является включающим, поэтому вы видите результаты, которые вы получили в своем первом запросе. Округление, которое вы видите во втором запросе, будет зависеть от того, какой именно тип данных datetime вы используете в своей таблице. (Кстати, я думаю, вы путаете секунды с сотыми секундами). Похоже, вы, вероятно, используете в своей таблице smalldatetime, и в этом случае время округляется до ближайшей минуты.
Если ваша таблица использует datetime, попробуйте явно преобразовать ваши @startDate и @endDate в значения DATETIME (CAST (@endDate AS DATETIME)).
Небольшое примечание ... даже для значений DATETIME SQL Server имеет точность только до 3/100 секунды, поэтому 11: 59: 59.999 будет округлено до 12: 00: 00.000.
В основном у вас есть три варианта:
1) МЕЖДУ CAST ('01 / 01/2008 00: 00: 00.000 'КАК DATETIME) И CAST ('03 / 31/2008 12: 59: 59.997' КАК DATETIME)
2) ГОД (my_date) = 2008 И МЕСЯЦ (my_date) МЕЖДУ 1 И 3
3) my_date> = CAST ('01 / 01/2008 00: 00: 00.000 'AS DATETIME) AND my_date <CAST ('04 / 01/2008 00: 00: 00.000' AS DATETIME)
Первый метод не очень интуитивен и, на мой взгляд, подвержен ошибкам. Второй метод убивает производительность, поскольку нельзя использовать индексы, и он становится намного сложнее, если у вас могут быть поиски, которые охватывают годы или начинаются / заканчиваются в середине месяцев. Третий метод, который предложил Роуленд, я считаю лучшим.
Просто попробуйте удалить время из поля даты следующим образом:
declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'
SELECT min(date),max(date) FROM view_Inspections
WHERE CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME) BETWEEN CAST(@startDate AS DATETIME) And CAST(@startDate AS DATETIME))
Это вернет все, от 01/01/2008 00:00:00 до 04/01/2008 11:59:59.999.
Если вы не хотите, чтобы 04/01 был включен, измените дату окончания на 03/31/2008.
Второй CAST (@startDate AS DATETIME) должен быть CAST (@endDate AS DATETIME)
Лучшее решение - просто создать поле BIGINT (10) с именем «julian» и сохранить его в формате ГГГГММДД.
Затем выполните запрос где юлиан> = '20120103' И юлиан <= '20120203'
Спасибо! Это очень помогает. @ G-Mastros: Это SmallDateTime - спасибо за объяснение поведения этого типа данных!