DATE的Oracle SQL比较返回错误的结果 [英] Oracle SQL comparison of DATEs returns wrong result

问题描述

我在数据库中有 REPORTDATE 列（ DATETIME ）类型。
我想从DATETIME中仅提取 DATE 的值，然后为每天执行 COUNT 将 WHERE 子句限制在某个特定日期之后的日期。

I have REPORTDATE column in database (DATETIME) type. I want to extract only DATE value from the DATETIME, then to do COUNT for each day and to put WHERE clause to restrict only dates later than some specific date.

所以我有这个子句：

SELECT to_char(REPORTDATE, 'DD.MM.YYYY') AS MY, COUNT(*) from INCIDENT
where to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013'

GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY')

它返回我的结果，但我可以注意到错误的结果，如： 30.10.2013 错误的结果。

It returns me results but but I can notice wrong result such as : 30.10.2013 which is wrong result.

如何解决这个问题？

推荐答案

WHERE to_char（REPORTDATE，'DD.MM.YYYY'）>'09.11.2013'

WHERE to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013'

你正在比较两个 STRINGS 。您需要比较 DATE 。正如我在其他答案中已经说过的那样，你需要按照DATE的计算方式保留日期。 TO_CHAR 用于显示， TO_DATE 是将字符串文字转换为DATE。

You are comparing two STRINGS. You need to compare the DATEs. As I already said in the other answer here, you need to leave the date as it is for DATE calculations. TO_CHAR is for display, and TO_DATE is to convert a string literal into DATE.

SELECT TO_CHAR(REPORTDATE, 'DD.MM.YYYY'),
  COUNT(*)
FROM TABLE
WHERE REPORTDATE > TO_DATE('09.11.2013', 'DD.MM.YYYY')
GROUP BY TO_CHAR(REPORTDATE, 'DD.MM.YYYY') 

此外，REPORTDATE是一个DATE列，因此它将具有datetime元素。因此，如果要在比较时排除时间元素，则需要使用 TRUNC

Also, REPORTDATE is a DATE column, hence it will have datetime element. So, if you want to exclude the time element while comparing, you need to use TRUNC

WHERE TRUNC(REPORTDATE) > TO_DATE('09.11.2013', 'DD.MM.YYYY')

然而， > STRUNC 在列之后，将会禁止该列的任何常规索引。从性能的角度来看，更好地使用日期范围条件。

However, applying TRUNC on the date column would suppress any regular index on that column. From performance point of view, better use a Date range condition.

例如，

WHERE REPORTDATE
BETWEEN 
        TO_DATE('09.11.2013', 'DD.MM.YYYY')
AND     
        TO_DATE('09.11.2013', 'DD.MM.YYYY') +1

这篇关于DATE的Oracle SQL比较返回错误的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！