• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • Last Modified:

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


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
   vWeek       VARCHAR2 (6);
   select to_char(v_date,'IYYYIW')
   into   vWeek
   from   dual;
   return vWeek;

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.
1 Solution
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 ?
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.  
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*/
 from dual


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now