如何仅根据 Oracle 中的日期部分比较两个 DATE 值? [英] How to compare two DATE values based only on date part in Oracle?

问题描述

我正在尝试使用以下查询获取过去 30 天的计数 -

I am trying to get counts for last 30 days with the following query -

SELECT date_occured, COUNT(*) FROM problem
WHERE date_occured >= (CURRENT_DATE - 30)
GROUP BY date_occured;

//date_occured field is of type DATE.

基本上，在我的查询中，我试图只比较条件 date_occured >= (CURRENT_DATE - 30) 中的日期部分，但它似乎也比较了时间.

Basically, in my query I am trying to compare only the date part in the condition date_occured >= (CURRENT_DATE - 30), but it seems to compare the time too.

我按如下方式尝试了 TRUNC -

I tried the TRUNC as follows -

TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30)

但是当运行查询时它永远不会返回.

But when run the query it never returns.

我也试过了-

SELECT date_occured, COUNT(*) FROM problem    
GROUP BY date_occured
HAVING TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30);

它再也不会回来了.

如何仅比较 Oracle 中两个 DATE 值的日期部分?

How can I compare only the date parts from two DATE values in Oracle?

推荐答案

对于这种情况你只需要TRUNC右边:

For this condition you only need to TRUNC the right-hand side:

WHERE date_occured >= TRUNC(CURRENT_DATE - 30)

为什么?因为如果 TRUNC(date_occured) 晚于 TRUNC(CURRENT_DATE - 30)，那么 TRUNC(date_occured) 之后的任何时刻也必然晚于 TRUNC(CURRENT_DATE - 30).

Why? Because if TRUNC(date_occured) is later than TRUNC(CURRENT_DATE - 30), then any moment in time after TRUNC(date_occured) is bound to be later than TRUNC(CURRENT_DATE - 30) too.

显然 date_occured >= TRUNC(date_occured) 总是正确的(想想看).

It is obviously always true that date_occured >= TRUNC(date_occured) (think about it).

逻辑说如果 A >= B 并且 B >= C 那么它遵循 A >= C

Logic says that if A >= B and B >= C then it follows that A >= C

现在替换:

• A : date_occured
• B : TRUNC(date_occured)
• C : TRUNC(CURRENT_DATE - 30)

这篇关于如何仅根据 Oracle 中的日期部分比较两个 DATE 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！