SQL Where 子句中的 Oracle DateTime？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6749369/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle DateTime in Where Clause?
提问by sanjeev40084
I have sql something like this:
我有这样的sql：
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'
-> 这将返回 10 行和 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?
有什么理由吗？
回答by Daniel Hilgarth
Yes: TIME_CREATED contains a date and a time. Use TRUNCto strip the time:
是： TIME_CREATED 包含日期和时间。使用TRUNC剥离时间：
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_CREATEDif it exists. An alternative approach without this problem is this:
更新：
正如 Dave Costa 在下面的评论中指出的那样，这将阻止 Oracle 使用该列的索引（TIME_CREATED如果存在）。没有这个问题的另一种方法是：
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
回答by davidsr
You can also use the following to include the TIME portion in your query:
您还可以使用以下内容在查询中包含 TIME 部分：
SELECT EMP_NAME
, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011 00:00:00', 'dd/mon/yyyy HH24:MI:SS');
回答by a_horse_with_no_name
This is because a DATEcolumn in Oracle also contains a time part. The result of the to_date()function is a date with the time set to 00:00:00and thus it probably doesn't match any rows in the table.
这是因为DATEOracle 中的列也包含时间部分。该to_date()函数的结果是一个时间设置为 的日期，00:00:00因此它可能与表中的任何行都不匹配。
You should use:
你应该使用：
SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE trunc(TIME_CREATED) = TO_DATE('26/JAN/2011','dd/mon/yyyy')
回答by Matas Vaitkevicius
You could also do:
你也可以这样做：
SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'
回答by Basanth Roy
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
正如其他人在上面评论的那样，使用 TRUNC 将阻止使用索引（如果 TIME_CREATED 上有索引）。为了避免这个问题，查询可以被构造为
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.
86399 比一天中的秒数少 1 秒。
