Re: Translate week number to date of the week.
Date: Tue, 27 Jan 2009 09:51:49 -0800 (PST)
Message-ID: <5ee97c8b-a53e-4361-be77-3f670c9d7906_at_b38g2000prf.googlegroups.com>
Comments embedded.
On Jan 27, 10:58 am, CrazyKarma <ska..._at_gmail.com> wrote:
> All,
>
> Is there a quick way to convert week number ( of the year) to start
> date of that week?
>
Define 'quick'.
> EX: If I pass week number as 1 then it should return 1/1/2009
That makes sense.
> if I pass week number as 7 then it should return 2/8/2009.
>
Why? Week 7 in 2009 starts with February 12.
> 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;
You want that, I expect. How does the following query strike you:
with date_wk as (
select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/ RRRR') + rownum - 1 dt
from dual
connect by level <= 366
),
wk_dt as (
select dt, to_char(dt, 'ww') wk_of_yr
from date_wk
)
select min(dt)
from wk_dt
where wk_of_yr = '&&1'
/
Let's see this in action:
SQL> _at_date_from_wk_of_yr_ex 01
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_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 = '01'
DT WK
--------- -- 01-JAN-09 01 02-JAN-09 01 03-JAN-09 01 04-JAN-09 01 05-JAN-09 01 06-JAN-09 01 07-JAN-09 01 01-JAN-10 01
8 rows selected.
Reporting the dates having the desired week of the year
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_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 = '01'
MIN(DT)
01-JAN-09 Report the starting date for that week number
Continue on for the next seven weeks
SQL>
SQL> _at_date_from_wk_of_yr_ex 02
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_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 = '02'
DT WK
--------- -- 08-JAN-09 02 09-JAN-09 02 10-JAN-09 02 11-JAN-09 02 12-JAN-09 02 13-JAN-09 02 14-JAN-09 02
7 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_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 = '02'
MIN(DT)
08-JAN-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 03
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_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 = '03'
DT WK
--------- -- 15-JAN-09 03 16-JAN-09 03 17-JAN-09 03 18-JAN-09 03 19-JAN-09 03 20-JAN-09 03 21-JAN-09 03
7 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_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 = '03'
MIN(DT)
15-JAN-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 04
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_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 = '04'
DT WK
--------- -- 22-JAN-09 04 23-JAN-09 04 24-JAN-09 04 25-JAN-09 04 26-JAN-09 04 27-JAN-09 04 28-JAN-09 04
7 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_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 = '04'
MIN(DT)
22-JAN-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 05
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_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 = '05'
DT WK
--------- -- 29-JAN-09 05 30-JAN-09 05 31-JAN-09 05 01-FEB-09 05 02-FEB-09 05 03-FEB-09 05 04-FEB-09 05
7 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_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 = '05'
MIN(DT)
29-JAN-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 06
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_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 = '06'
DT WK
--------- -- 05-FEB-09 06 06-FEB-09 06 07-FEB-09 06 08-FEB-09 06 09-FEB-09 06 10-FEB-09 06 11-FEB-09 06
7 rows selected.
Notice February 8 occurs in week 6, not week 7.
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_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 = '06'
MIN(DT)
05-FEB-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 07
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_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 = '07'
DT WK
--------- -- 12-FEB-09 07 13-FEB-09 07 14-FEB-09 07 15-FEB-09 07 16-FEB-09 07 17-FEB-09 07 18-FEB-09 07
7 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_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 = '07'
MIN(DT)
12-FEB-09
SQL>
SQL> _at_date_from_wk_of_yr_ex 08
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_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 = '08'
DT WK
--------- -- 19-FEB-09 08 20-FEB-09 08 21-FEB-09 08 22-FEB-09 08 23-FEB-09 08 24-FEB-09 08 25-FEB-09 08
7 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_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 = '08'
MIN(DT)
19-FEB-09 SQL>
>
> Thanks a bunch
If you want a function I leave it to you to rewrite this.
David Fitzjarrell Received on Tue Jan 27 2009 - 11:51:49 CST