ITPub博客

首页 > IT基础架构 > 网络安全 > about date manipulation(zt asktom)

about date manipulation(zt asktom)

原创 网络安全 作者:jametong 时间:2005-04-26 05:29:59 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280630