Oracle DateTime in Where Clause

Question

I have sql something like this   SELECT EMP NAME  DEPT FROM EMPLOYEE WHERE TIME CREATED  gt   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

User · Answer

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

User · Answer

You can also use the following to include the TIME portion in your query   SELECT EMP NAME        DEPT   FROM EMPLOYEE   WHERE TIME CREATED  gt   TO DATE  26 JAN 2011 00 00 00    dd mon yyyy HH24 MI SS

User · Answer

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

User · Answer

You could also do   SELECT EMP NAME  DEPT FROM EMPLOYEE WHERE TRUNC TIME CREATED    DATE  2011-01-26

User · Answer

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  gt   TO DATE  26 JAN 2011   dd mon yyyy          AND TIME CREATED  lt  TO DATE  26 JAN 2011   dd mon yyyy     1

[sql] Oracle DateTime in Where Clause?

Examples related to sql

Examples related to oracle

Examples related to time

Examples related to date-arithmetic