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