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?

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