Oracle DateTime in Where Clause?

Question

I have sql something like this:

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

-> This returns 10 rows and TIME_CREATED = '26-JAN-2011'

Now when i do this i don't get any rows back,

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

-> Took the greater than out

Any reason why?

Multiplechoice

You should avoid language dependent date formats. That can cause trouble on different systems. You should use 01 instead of JAN (plus the approriate format of course) to make sure your code runs without problem on any system. – Nomadic

Answer 1

Yes: TIME_CREATED contains a date and a time. Use TRUNC to strip the time:

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

UPDATE:
As Dave Costa points out in the comment below, this will prevent Oracle from using the index of the column TIME_CREATED if it exists. An alternative approach without this problem is this:

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

Maddocks

Note that this approach will prevent the use of an index on TIME_CREATED, if one exists. – Spectre

Thank you posting the solution. It was quick and easy to find. While I have worked on other DBMS such as Ingres, MS-SQL, MS-Access, and DB2, I have not worked with Oracle before my current assignment. – Ashwell

Why not use BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1? – Hoehne

@ajeh: I don't like between because of the ambiguity. It sounds as if it is exclusive the bounds when in fact it is inclusive. That's why I avoid it. Furthermore, in this concrete example, it wouldn't be the same. – Maddocks

Answer 2

You can also use the following to include the TIME portion in your query:

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

Quintain

Answer 3

You could also do:

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

Caniff

Answer 4

This is because a DATE column in Oracle also contains a time part. The result of the to_date() function is a date with the time set to 00:00:00 and thus it probably doesn't match any rows in the table.

You should use:

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

Nomadic

Answer 5

As other people have commented above, using TRUNC will prevent the use of indexes (if there was an index on TIME_CREATED). To avoid that problem, the query can be structured as

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 being 1 second less than the number of seconds in a day.

Megalocardia