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

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

1. pks
Junior Member
Join Date
Aug 2001
Location
Washington, USA
Posts
3

## 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)

2. Since you can't use the "ww" format for input, I would write a pl/sql function.

3. pks
Junior Member
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

4. 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 )

5. Junior Member
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. Junior Member
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. Junior Member
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. I'll never stop being amazed at what you can do in SQL...

#### Posting Permissions

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