Re: Translate week number to date of the week.

From: CrazyKarma <skalki_at_gmail.com>
Date: Tue, 27 Jan 2009 12:01:04 -0800 (PST)
Message-ID: <0a286b3d-3b29-44f5-9b10-fc3014568330_at_e1g2000pra.googlegroups.com>

On Jan 27, 12:49 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks David. Awesome and quick. Excellent. I am using your solution. I did convert that into a function.

Thanks to Michel as well for this idea. Received on Tue Jan 27 2009 - 14:01:04 CST