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.
lhalkapeelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SMartinHamburgCommented:
to_char(v_date,'IW')  should deliver 'ISO' week starting monday and selcting week on of the year
depending wether January 1st falls on Friday, Saturday or Sunday (then the week including
January 1st is  last week of previous year) or falls on a Monday trough Thursday (then the week
is the first week of the new year).
Is it taht what you need ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lhalkapeelAuthor Commented:
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.  
0
alexfrlCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.