The challenge here is to establish the number of weeks that have elapsed between two specified dates. A week number can easily be returned using the IW format (seel below)
SELECT TO_CHAR(TO_DATE('26/10/07','DD/MM/YY'),'YYYYIW') -
TO_CHAR(TO_DATE('19/10/07','DD/MM/YY'),'YYYYIW') X FROM DUAL
The correct answer is returned which is '1' (i.e. one week number between the two dates)
However, the problem I encounter surfaces when I work on dates that span a calendar year (see below)
SELECT TO_CHAR(TO_DATE('04/01/08','DD/MM/YY'),'YYYYIW') -
TO_CHAR(TO_DATE('28/12/07','DD/MM/YY'),'YYYYIW') X FROM DUAL
Once again, the answer I am looking for is '1' but '49' is returned because of the calendar year.
I was tempted to simply establish the number of days that elapse between the two dates and divide by 7 but this will not work because we must remember that EVEN ONE DAY between the two dates can theoretically fall into different weeks (i.e. Any Sunday followed by the following Monday should return a different week).
I hope there is enough for you to work on with the info above but please contact me should more info be needed.