Re: Translate week number to date of the week.

From: ddf <oratune_at_msn.com>
Date: Tue, 27 Jan 2009 10:49:50 -0800 (PST)
Message-ID: <e6a97281-704f-40cd-9423-998cf61ec1c5_at_a39g2000prl.googlegroups.com>

On Jan 27, 11:32 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "CrazyKarma" <ska..._at_gmail.com> a écrit dans le message de news:
> a4f012e5-0ae4-426d-98bc-e23751344..._at_f40g2000pri.googlegroups.com...
> | All,
> |
> |   Is there a quick way to convert week number ( of the year) to start
> | date of that week?
> |
> | EX: If I pass week number as 1 then it should return 1/1/2009
> |      if I pass week  number as 7 then it should return 2/8/2009.
> |
> | This gets if I know the date
> |
> | select trunc(sysdate, 'd') from dual
> |
> | but I need something equivalet to that  as
> |
> | select FN(7) from dual;
> |
> | Thanks a bunch
>
> maybe:
> select greatest(trunc(sysdate,'year'),trunc(trunc(sysdate,'year'),'iw')+7*(&input-­1))
> from dual
> /
>
> Regards
> Michel

And this uses the ISO definition of the week of the year which can produce different results:

Use the 'WW' format to compute the week of the year -- January 1 always starts week 1

SQL> _at_date_from_wk_of_yr_ex 1
SQL> with date_wk as (
  2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/ RRRR') + rownum - 1 dt

  3          from dual
  4          connect by level <= 366

  5 ),
  6 wk_dt as (

  7          select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8          from date_wk

  9 )
 10 select dt, wk_of_yr
 11 from wk_dt
 12 where wk_of_yr = &&1
 13 /
old 12: where wk_of_yr = &&1
new 12: where wk_of_yr = 1

DT WK_OF_YR

--------- ----------
01-JAN-09          1
02-JAN-09          1
03-JAN-09          1
04-JAN-09          1
05-JAN-09          1
06-JAN-09          1
07-JAN-09          1
01-JAN-10          1

8 rows selected.

SQL>
SQL> with date_wk as (
  2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/ RRRR') + rownum - 1 dt

  3          from dual
  4          connect by level <= 366

  5 ),
  6 wk_dt as (

  7          select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8          from date_wk

  9 )
 10 select min(dt)
 11 from wk_dt
 12 where wk_of_yr = &&1
 13 /
old 12: where wk_of_yr = &&1
new 12: where wk_of_yr = 1

MIN(DT)

---

01-JAN-09 Use the ISO definition for the week of the year -- January 4th is always in week 1

SQL>
SQL> with date_wk as (
  2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/ RRRR') + rownum - 4 dt

  3          from dual
  4          connect by level <= 366

  5 ),
  6 wk_dt as (

  7          select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8          from date_wk

  9 )
 10 select dt, wk_of_yr
 11 from wk_dt
 12 where wk_of_yr = &&1
 13 /
old 12: where wk_of_yr = &&1
new 12: where wk_of_yr = 1

DT WK_OF_YR

--------- ----------
29-DEC-08          1
30-DEC-08          1
31-DEC-08          1
01-JAN-09          1
02-JAN-09          1
03-JAN-09          1
04-JAN-09          1

7 rows selected.

SQL>
SQL> with date_wk as (
  2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/ RRRR') + rownum - 4 dt

  3          from dual
  4          connect by level <= 366

  5 ),
  6 wk_dt as (

  7          select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8          from date_wk

  9 )
 10 select min(dt)
 11 from wk_dt
 12 where wk_of_yr = &&1
 13 /
old 12: where wk_of_yr = &&1
new 12: where wk_of_yr = 1

MIN(DT)

---

29-DEC-08 SQL> David Fitzjarrell Received on Tue Jan 27 2009 - 12:49:50 CST