Click here to Skip to main content
15,307,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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:
SQL
SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;

The result is:
txt
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:

SQL
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:
SQL
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900