Oracle DateTime in Where Clause?

84

Mam sql coś takiego:

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

-> Zwraca 10 wierszy i TIME_CREATED = '26 -JAN-2011 '

Teraz, kiedy to robię, nie odzyskuję żadnych wierszy

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

-> Wziął większe niż na zewnątrz

Jakiś powód dlaczego?

— sanjeev40084
źródło

4

Należy unikać formatów daty zależnych od języka. Może to powodować problemy w różnych systemach. Powinieneś użyć 01zamiast JAN(plus oczywiście odpowiedni format), aby upewnić się, że twój kod działa bez problemów w każdym systemie.

— a_horse_with_no_name

149

Tak: TIME_CREATED zawiera datę i godzinę . Użyj, TRUNCaby usunąć czas:

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

AKTUALIZACJA:
Jak zauważa Dave Costa w poniższym komentarzu, uniemożliwi to Oracle korzystanie z indeksu kolumny, TIME_CREATEDjeśli istnieje. Alternatywnym podejściem bez tego problemu jest:

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

— Daniel Hilgarth
źródło

14

Zauważ, że to podejście uniemożliwi użycie indeksu w TIME_CREATED, jeśli taki istnieje.

— Dave Costa,

Dziękuję za wysłanie rozwiązania. To było szybkie i łatwe do znalezienia. Chociaż pracowałem nad innymi systemami DBMS, takimi jak Ingres, MS-SQL, MS-Access i DB2, nie pracowałem z Oracle przed moim obecnym zadaniem.

— Jason TEPOORTEN

Dlaczego nie używać BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1?

— ajeh

2

@ajeh: Nie lubię z betweenpowodu niejednoznaczności. Brzmi to tak, jakby wyłączało granice, podczas gdy w rzeczywistości obejmuje. Dlatego tego unikam. Ponadto w tym konkretnym przykładzie nie byłoby to to samo.

— Daniel Hilgarth,

28

Możesz również użyć następujących elementów, aby uwzględnić część CZAS w zapytaniu:

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

— davidsr
źródło

8

Możesz też:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'

— Matas Vaitkevicius
źródło

7

Dzieje się tak, ponieważ DATEkolumna w Oracle zawiera również część czasu. Wynikiem to_date()funkcji jest data z ustawioną godziną, 00:00:00dlatego prawdopodobnie nie pasuje ona do żadnych wierszy w tabeli.

Powinieneś użyć:

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

— koń bez imienia
źródło

5

Jak skomentowały inne osoby powyżej, użycie TRUNC uniemożliwi użycie indeksów (jeśli indeks istniał na TIME_CREATED). Aby uniknąć tego problemu, zapytanie może mieć strukturę

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 to 1 sekunda mniej niż liczba sekund w ciągu dnia.

— Basanth Roy
źródło