Oracle DateTime в предложении «Где»?

У меня есть sql что-то вроде этого:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy')

-> Это возвращает 10 строк и TIME_CREATED = '26-JAN-2011'

Теперь, когда я это делаю, я не получаю никаких строк,

SELECT EMP_NAME, DEPT
    FROM EMPLOYEE
    WHERE TIME_CREATED = TO_DATE('26/JAN/2011','dd/mon/yyyy')

-> Взял больше, чем вышел

Любая причина, почему?


sql time oracle date-arithmetic
person sanjeev40084    schedule 19.07.2011    source источник
comment
Вам следует избегать форматов даты, зависящих от языка. Это может вызвать проблемы в разных системах. Вы должны использовать 01 вместо JAN (плюс соответствующий формат, конечно), чтобы убедиться, что ваш код работает без проблем в любой системе.   -  person a_horse_with_no_name    schedule 20.04.2013

Ответы (5)


arrow_upward
156
arrow_downward

Да: TIME_CREATED содержит дату и время. Используйте TRUNC, чтобы убрать время:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = TO_DATE('26/JAN/2011','dd/mon/yyyy')

ОБНОВЛЕНИЕ:
Как отмечает Дэйв Коста в комментарии ниже, это не позволит Oracle использовать индекс столбца TIME_CREATED, если он существует. Альтернативный подход без этой проблемы таков:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') 
      AND TIME_CREATED < TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1
person Daniel Hilgarth    schedule 19.07.2011
comment
Обратите внимание, что этот подход предотвратит использование индекса для TIME_CREATED, если он существует. - person Dave Costa; 19.07.2011
comment
Спасибо, что опубликовали решение. Это было быстро и легко найти. Хотя я работал с другими СУБД, такими как Ingres, MS-SQL, MS-Access и DB2, я не работал с Oracle до моего текущего назначения. - person Jason TEPOORTEN; 19.10.2012
comment
Почему бы не использовать BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1? - person ajeh; 04.12.2013
comment
@ajeh: мне не нравится between из-за двусмысленности. Звучит так, как будто это исключительные границы, когда на самом деле они включают. Вот почему я избегаю этого. Кроме того, в этом конкретном примере это было бы не то же самое. - person Daniel Hilgarth; 06.12.2013

arrow_upward
32
arrow_downward

Вы также можете использовать следующее, чтобы включить часть TIME в свой запрос:

SELECT EMP_NAME
     , DEPT
  FROM EMPLOYEE 
 WHERE TIME_CREATED >= TO_DATE('26/JAN/2011 00:00:00', 'dd/mon/yyyy HH24:MI:SS');
person davidsr    schedule 20.07.2011

arrow_upward
9
arrow_downward

Вы также можете сделать:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'
person Matas Vaitkevicius    schedule 17.05.2016

arrow_upward
8
arrow_downward

Это связано с тем, что столбец DATE в Oracle также содержит часть времени. Результатом функции to_date() является дата со временем, установленным на 00:00:00, и, следовательно, она, вероятно, не соответствует ни одной строке в таблице.

Вы должны использовать:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE trunc(TIME_CREATED) = TO_DATE('26/JAN/2011','dd/mon/yyyy')
person a_horse_with_no_name    schedule 19.07.2011

arrow_upward
5
arrow_downward

Как отмечали выше другие люди, использование TRUNC предотвратит использование индексов (если индекс был на TIME_CREATED). Чтобы избежать этой проблемы, запрос может быть структурирован как

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') 
            AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + INTERVAL '86399' second;

86399 на 1 секунду меньше количества секунд в сутках.

person Basanth Roy    schedule 19.07.2011