查找两个日期之间的差异 [英] To Find the difference between two dates

问题描述

我该如何在oracle中做一个程序来检查两个日期之间的差额是否大于x个月和y周?

How can i do a program in oracle to check the difference between two dates are greater than x months and y weeks??

推荐答案

答案是LAST_DAY文档中的>隐藏" :
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions072.htm#i83733 [ ^ ]

从那里:

The answer is hidden in the documentation for LAST_DAY:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions072.htm#i83733[^]

From there:

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;

其结果是:

The result is:

SYSDATE   Last       Days Left
--------- --------- ----------
30-MAY-01 31-MAY-01          1

看到第三行吗?您只需从另一个日期中减去一个日期即可.
因此，您需要从DUAL中选择SELECT DATE1-DATE2并获得一个数字，该数字是一个天数.
除以7，就可以得到周.

这是一个小测试:

See the third line? You just subtract one date from another.
So you do SELECT DATE1 - DATE2 FROM DUAL and get a number, which is a number of days.
Divide by seven, you''ll get the weeks.

Here is a little test:

SELECT 
  SYSDATE - TO_DATE('2012-03-01', 'YYYY-MM-DD') "Time In Month",
 (SYSDATE - TO_DATE('2012-03-01', 'YYYY-MM-DD')) / 7.0 "Weeks" 
from dual;

结果:

And the result:

Time In Month          Weeks                  
---------------------- ---------------------- 
25.659                 3.666                  

1 rows selected

要查看月份，我建议这样做:

To check the months, I''d suggest this:

SELECT TO_DATE('2012-01-01', 'YYYY-MM-DD') FROM DUAL;
SELECT SYSDATE, TO_DATE('2012-01-01', 'YYYY-MM-DD') "TEST_DATE", 
       ADD_MONTHS(TO_DATE('2012-01-01', 'YYYY-MM-DD'), 4) "Test + 4 months", 
       CASE WHEN ADD_MONTHS(TO_DATE('2012-01-01', 'YYYY-MM-DD'), 4) > SYSDATE THEN 'Less than 4 months ago'
       ELSE 'More than 4 months ago'
       END /* CASE */ "Test result"
   FROM DUAL;

结果是:

The results are:

TO_DATE('2012-01-01','YYYY-MM-DD') 
------------------------- 
01-JAN-12                 

1 rows selected


SYSDATE                   TEST_DATE                 Test + 4 months           Test result            
------------------------- ------------------------- ------------------------- ---------------------- 
26-MAR-12                 01-JAN-12                 01-MAY-12                 Less than 4 months ago 

1 rows selected

希望这会有所帮助，
巴勃罗.

Hope this helps,
Pablo.

这篇关于查找两个日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！