DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to get a Monday's date for a given Year & Week number?

  1. #1
    Join Date
    Aug 2001
    Location
    Washington, USA
    Posts
    3

    Question How to get a Monday's date for a given Year & Week number?

    How to get the Monday's date for an any given Year and week number (e.g. 2001/01 = Monday 1/1/2001)


    Pras :cool:

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since you can't use the "ww" format for input, I would write a pl/sql function.
    Jeff Hunter

  3. #3
    Join Date
    Aug 2001
    Location
    Washington, USA
    Posts
    3
    What will be the code in pl/sql to get date(Monday) for a given year and week number as per the problem described above.

    Thanks
    Pras :cool:

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You're kidding, right?

    Look at the to_date function ( http://technet.oracle.com/docs/produ...0datyp.htm#814 )

    Look at the Date format models at ( http://technet.oracle.com/docs/produ...elem.htm#34926 )

    Look at CREATE FUNCTION ( http://technet.oracle.com/docs/produ...pckgs.htm#4183 )
    Jeff Hunter

  5. #5
    Join Date
    May 2000
    Posts
    58
    select to_date('01-JAN-&YYYY') + (&wk -1)*7 + r - 1
    from dual , ( select rownum r from tab where rownum < 7 )
    where to_char(to_date('01-JAN-&YYYY') +(&wk -1)*7 + r -1 , 'DY') = 'MON'

    You might have to change the logic to derive the first day of the first week in the given year and use that instead of '01-JAN-&YYYY'.

    This is the closest i could get..

  6. #6
    Join Date
    Jun 2001
    Location
    India
    Posts
    19
    Originally posted by Victoria
    select to_date('01-JAN-&YYYY') + (&wk -1)*7 + r - 1
    from dual , ( select rownum r from tab where rownum < 7 )
    where to_char(to_date('01-JAN-&YYYY') +(&wk -1)*7 + r -1 , 'DY') = 'MON'

    You might have to change the logic to derive the first day of the first week in the given year and use that instead of '01-JAN-&YYYY'.

    This is the closest i could get..
    Hai victoria,

    Can you explain me this logic please.. why u r using r variable

  7. #7
    Join Date
    May 2000
    Posts
    58
    Lets say wk = 2, then
    to_date('01-JAN-&YYYY') + (&wk -1)*7 will give me the first day of the 2nd week following '01-JAN-&YYYY' .
    Now from that date onwards, you have to test if it is a Monday and you have to test this for the 7 days in that week.
    Since there is no loop functionality in sql, I used an inline view
    ( select rownum r from tab where rownum < 7 ) which is 1 through 6. But I had to subtract 1 because I dont want to miss the start date.



  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'll never stop being amazed at what you can do in SQL...
    Jeff Hunter

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