asked on

Returning the week of the year based on user-defined week

Hello,

We wish to determine the week of the year but out week is defined as starting on Monday and ending on Sunday. We are currently using a procedure like this:
FUNCTION schd_week_cx (v_date DATE)
RETURN VARCHAR2
IS
vWeek VARCHAR2 (6);

BEGIN
select to_char(v_date,'IYYYIW')
into vWeek
from dual;
return vWeek;
END;

But it returns the week based on the standard Saturday - Sunday criteria. It would be nice to avoid writing a lot of custom code, any ideas on an elegant solution would be greatly appreciated. We are under an extremely tight deadline.

ASKER CERTIFIED SOLUTION

SMartinHamburg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lhalkapeel

ASKER

Never mind, our mistake. I was writing on behalf of a developer on the team who said that this date functionality did not work as we thought. I should have tested the functionality myself prior to posting to this forum. Thank you for your prompt response.

alexfrl

Here is your formula. Use it.

select trunc((mod(to_char(trunc(&THE_DATE,'Year'),'D')+5,7)+to_char(&THE_DATE,'DDD')+5)/7)
from dual

In details:

select trunc((
mod(to_char(trunc(THE_DATE,'Year'),'D')-2/*THE JET LAG*/+7,7) /* Start day (week day) number was 1 or 2 then now is 6 or 7, 3 is 1 and so on */
+to_char(THE_DATE,'DDD')-1 /* the jump in days to THE_DATE */
+6/*1,2,3,4,5,6,7 + 6 = 7,8..13 => /7 truncated = 1*/
)/7)
from dual