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

问题描述

我在数据库 (DATETIME) 类型中有 REPORTDATE 列.我只想从 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')

但是，在日期列上应用TRUNC会抑制该列上的任何常规索引.从性能的角度来看，最好使用日期范围条件.

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

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