# Thread: WeekNumber to start of week date

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. 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;```

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. 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
;```

Join Date
Jan 2001
Posts
2,828
Hi Pavb

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

6. Junior Member
Join Date
Dec 2007
Posts
82
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..

Join Date
Jan 2001
Posts
2,828
Hi mdvreddy

Absolutely Brilliant :-) and quite elegant solution too

regards
Hrishy

8. Senior Member
Join Date
Apr 2006
Posts
377
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```

Join Date
Jan 2001
Posts
2,828
Hi Ebrian

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

regards
Hrishy

10. Junior Member
Join Date
Dec 2007
Posts
82
..

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