DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: WeekNumber to start of week date

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    WeekNumber to start of week date

    Hi

    Does anybody know of a way to convert a given weeknumber to date.

    Say for example if i input weeknumber as 27 it should return the monday of that week and the return date should be 01/07/2008

    regards
    Hrishy

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by hrishy
    Say for example if i input weeknumber as 27 it should return the monday of that week and the return date should be 01/07/2008
    Nobody can do that... problem is JUL-01-2008 wasn't a Monday, it was a Tueday

    How about something like...
    Code:
    select  next_day(trunc((to_date('01/01/2008', 'MM/DD/YYYY') + ( (27 - 1) * 7 ))), 'Monday')
    from    dual;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Thanks for the reply.

    But i am getting the right answer only when i do 27-2 i.e for week number 27 i should be doing 27-2 any idea why

    Code:
    select
    next_day(trunc((to_date('01/01/2008', 'DD/MM/YYYY') + ( (27-2) * 7 ))), 'Monday')
    from    dual
    
    NEXT_DAY(
    ---------
    30-JUN-08
    regards
    Hrishy

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I may have an idea... shouldn't we adjust our calendar? the first week of the year most of the time does not begins the first day of such a week.
    Code:
    select  next_day(trunc(                                                 -- We want to get a next_day...
            (to_date('01/01/2008', 'DD/MM/YYYY') +                          --    set first day of the year
            ( ((27-1) * 7) -                                                --    set WEEK number e.g.: 27
            (to_char(to_date('01/01/2008', 'DD/MM/YYYY'), 'D') - 1) ))),    --    adjust first day of the year/week
            'Monday')                                                       -- ...which is a Monday!
    from    dual
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Pavb

    Brilliant that works thank you very very much ofr the explanation
    regards
    Hrishy

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    "I love when a plan comes together!"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2007
    Posts
    82
    Quote Originally Posted by hrishy
    Hi Pavb

    Brilliant that works thank you very very much ofr the explanation
    regards
    Hrishy
    the correct one is :

    SELECT TRUNC(TO_DATE(2008||' '||((27-1)*7),'YYYY DDD'))
    FROM dual;

    27-> week number..

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi mdvreddy

    Absolutely Brilliant :-) and quite elegant solution too

    regards
    Hrishy

  9. #9
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by mdvreddy
    the correct one is :

    SELECT TRUNC(TO_DATE(2008||' '||((27-1)*7),'YYYY DDD'))
    FROM dual;

    27-> week number..
    Don't be so fast to discount PAVB's solution. His will actually work next year, whereas yours will NOT.

    Code:
    SQL> --- Your query next year
    SQL> SELECT TRUNC(TO_DATE(2009||' '||((27-1)*7),'YYYY DDD'))
      2  FROM dual;
    
    TRUNC(TO_
    ---------
    01-JUL-09    <--- This is a Wednesday
    In addition to PAVB's "accurate" query, a territory independent option could be:
    Code:
    SQL> select trunc(trunc(sysdate, 'Y')+(27-1)*7, 'IW') from dual;
    
    TRUNC(TRU
    ---------
    30-JUN-08
    
    SQL> --- Next Year
    SQL> select trunc(trunc(sysdate+365, 'Y')+(27-1)*7, 'IW') from dual;
    
    TRUNC(TRU
    ---------
    29-JUN-09

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Ebrian

    Thanks a million for showing me the importance of testing :-)

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width