Click here to Skip to main content
14,668,490 members
Rate this:
Please Sign up or sign in to vote.
See more:
How can i do a program in oracle to check the difference between two dates are greater than x months and y weeks??
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100