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

Posted on 2004-11-29
Last Modified: 2010-07-27

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.
Question by:lhalkapeel
    LVL 5

    Accepted 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 ?

    Author Comment

    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.  
    LVL 5

    Expert Comment

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now