about date manipulation(zt asktom)

How to get the the date of first day of the week for a given week #?  For 
example, the beginning day for week# 34 in 2003 is AUG-18-2003, how do I find 
the beginning date for the same week # 34 in 2002?  'iw' can't be used in the 
to_date function.

Thanks! 

Followup:

well, we can use the fact that at least from 1800 to 2199, jan 4th is in the first week... this: ops$tkyte@ORA920> select * 2 from ( 3 select dt1, to_char( dt1, 'yyyy' ), 4 to_char( dt4, 'iw' ), 5 decode( to_char( dt4, 'iw' ), '01', null, '******' ) xxx 6 from ( 7 select add_months( to_date( '01-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt1, 8 add_months( to_date( '02-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt2, 9 add_months( to_date( '03-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt3, 10 add_months( to_date( '04-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt4, 11 add_months( to_date( '05-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt5, 12 add_months( to_date( '06-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt6, 13 add_months( to_date( '07-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt7 14 from all_objects 15 where rownum <= 400 16 ) 17 ) 18 where xxx is not null 19 / no rows selected shows that. Then, we can use the fact that next_day(dt-7,'mon') returns the monday of that week that DT falls in. sooo, it would seem that if we take jan-4th of the year of interest. we add to that the (week-2)* 7 days (-1 becuase we start at week one, -1 again because we back off 7 days) . and then ask for the next monday -- we get it: ops$tkyte@ORA920> variable year varchar2(4) ops$tkyte@ORA920> variable week number ops$tkyte@ORA920> ops$tkyte@ORA920> exec :year := '2003'; :week := 34 PL/SQL procedure successfully completed. ops$tkyte@ORA920> ops$tkyte@ORA920> select next_day( to_date( '04-jan-' || :year, 'dd-mon-yyyy' ) + (:week-2)*7, 'mon' ) 2 from dual 3 / NEXT_DAY(TO_DATE('04 -------------------- 18-aug-2003 00:00:00 this query generates all of the "first days of the week" by IW week for a year: ops$tkyte@ORA920> select year, week, 2 next_day( to_date( '04-jan-' || year, 'dd-mon-yyyy' ) + (week-2)*7, 'mon' ) 3 from (select '2003' year, rownum week from all_objects where rownum <= 53 ) 4 / YEAR WEEK NEXT_DAY(TO_DATE('04 ---- ---------- -------------------- 2003 1 30-dec-2002 00:00:00 2003 2 06-jan-2003 00:00:00 2003 3 13-jan-2003 00:00:00 2003 4 20-jan-2003 00:00:00 2003 5 27-jan-2003 00:00:00 2003 6 03-feb-2003 00:00:00 2003 7 10-feb-2003 00:00:00 2003 8 17-feb-2003 00:00:00 2003 9 24-feb-2003 00:00:00 2003 10 03-mar-2003 00:00:00 2003 11 10-mar-2003 00:00:00 2003 12 17-mar-2003 00:00:00 2003 13 24-mar-2003 00:00:00 2003 14 31-mar-2003 00:00:00 2003 15 07-apr-2003 00:00:00 2003 16 14-apr-2003 00:00:00 2003 17 21-apr-2003 00:00:00 2003 18 28-apr-2003 00:00:00 2003 19 05-may-2003 00:00:00 2003 20 12-may-2003 00:00:00 2003 21 19-may-2003 00:00:00 2003 22 26-may-2003 00:00:00 2003 23 02-jun-2003 00:00:00 2003 24 09-jun-2003 00:00:00 2003 25 16-jun-2003 00:00:00 2003 26 23-jun-2003 00:00:00 2003 27 30-jun-2003 00:00:00 2003 28 07-jul-2003 00:00:00 2003 29 14-jul-2003 00:00:00 2003 30 21-jul-2003 00:00:00 2003 31 28-jul-2003 00:00:00 2003 32 04-aug-2003 00:00:00 2003 33 11-aug-2003 00:00:00 2003 34 18-aug-2003 00:00:00 2003 35 25-aug-2003 00:00:00 2003 36 01-sep-2003 00:00:00 2003 37 08-sep-2003 00:00:00 2003 38 15-sep-2003 00:00:00 2003 39 22-sep-2003 00:00:00 2003 40 29-sep-2003 00:00:00 2003 41 06-oct-2003 00:00:00 2003 42 13-oct-2003 00:00:00 2003 43 20-oct-2003 00:00:00 2003 44 27-oct-2003 00:00:00 2003 45 03-nov-2003 00:00:00 2003 46 10-nov-2003 00:00:00 2003 47 17-nov-2003 00:00:00 2003 48 24-nov-2003 00:00:00 2003 49 01-dec-2003 00:00:00 2003 50 08-dec-2003 00:00:00 2003 51 15-dec-2003 00:00:00 2003 52 22-dec-2003 00:00:00 2003 53 29-dec-2003 00:00:00 53 rows selected. please -- check out the boundary value conditions!!! (eg: test this, date things can be tricky)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796908/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796908/