在哪里子句中的 Oracle 日期时间? ？

我有这样的东西:

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

- > 这将返回10行，TIME _ CREATION =’26-JAN-2011’

现在我这样做，我没有得到任何排回来,

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

- > 带走比出去更伟大的

为什么？

543809 次浏览
小开
最佳答案

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
小开

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')
小开

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.

小开

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, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'

提交答案