Oracle DateTime在哪里子句? [英] Oracle DateTime in Where Clause?
本文介绍了Oracle DateTime在哪里子句?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有类似这样的sql:
I have sql something like this:
SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy')
->这将返回10行,并且TIME_CREATED = '26 -JAN-2011'
-> 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
任何理由吗?
推荐答案
是:TIME_CREATED包含日期和时间.使用TRUNC
删除时间:
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')
更新:
正如Dave Costa在下面的注释中指出的那样,这将防止Oracle使用列TIME_CREATED
的索引(如果存在).没有此问题的另一种方法是:
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
这篇关于Oracle DateTime在哪里子句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文