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

问题描述

我想使用以下查询获取过去30天的计数：

  SELECT date_occured，COUNT问题
 WHERE date_occured> =（CURRENT_DATE  -  30）
 GROUP BY date_occured; 
 
 // date_occured字段的类型为DATE。 
  

基本上，在我的查询中，我只想比较条件 date_occured> =（CURRENT_DATE - 30），但似乎也要比较时间。

  TRUNC（date_occured）> = TRUNC（CURRENT_DATE  -  30）
  pre> 
 
 
但是当运行查询时它从不返回。
 
 
 
我也试过了 -  
 
 
  SELECT date_occured，COUNT（*）FROM issue 
 GROUP BY date_occured 
 HAVING TRUNC（date_occured）> = TRUNC（CURRENT_DATE  - 30）; 
  

再也不会返回。

我可以只比较Oracle中两个DATE值的日期部分。

解决方案

对于这种情况你只需要TRUNC，

  WHERE date_occured> = TRUNC（CURRENT_DATE  -  30）
  

为什么？因为如果TRUNC（date_occured）晚于TRUNC（CURRENT_DATE - 30），则在TRUNC（date_occured）之后的任何时刻都必须晚于TRUNC（CURRENT_DATE - 30）。

很明显，date_occured> = TRUNC（date_occured）（想想它）总是如此。

逻辑如果A> = B并且B> = C，那么A> = C

现在替换为：

• A：date_occured


• B：TRUNC（date_occured）


• C：TRUNC（CURRENT_DATE - 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.

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.

I tried the TRUNC as follows -

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

But when run the query it never returns.

I also tried -

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

Again it never returns.

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

解决方案

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

WHERE 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.

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

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

Now substitute:

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

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