# WeekNumber to start of week date

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 07-02-2008, 12:07 PM
hrishy
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
• 07-02-2008, 12:27 PM
PAVB
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;```
• 07-03-2008, 04:43 AM
hrishy
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
• 07-03-2008, 05:25 AM
PAVB
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 ;```
• 07-03-2008, 05:55 AM
hrishy
Hi Pavb

Brilliant that works thank you very very much ofr the explanation
regards
Hrishy
• 07-03-2008, 06:26 AM
PAVB
"I love when a plan comes together!" ;)
• 07-04-2008, 07:51 AM
mdvreddy
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..
• 07-04-2008, 08:37 AM
hrishy
Hi mdvreddy

Absolutely Brilliant :-) and quite elegant solution too

regards
Hrishy
• 07-05-2008, 02:07 AM
ebrian
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```
• 07-06-2008, 02:52 AM
hrishy
Hi Ebrian

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

regards
Hrishy
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last