Oracle DateTime in Where Clause?
Yes: TIME_CREATED contains a date and a time. Use TRUNC to strip the time:
SELECT EMP_NAME, DEPTFROM EMPLOYEEWHERE 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, DEPTFROM EMPLOYEEWHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') AND TIME_CREATED < TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1
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');
You could also do:
SELECT EMP_NAME, DEPTFROM EMPLOYEEWHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'