oracle tips : 如何确定今天是本月的第几周.

===========================================================

如何确定今天是本月的第几周.

===========================================================

作者: jametong(http://jametong.itpub.net)
发表于: 2005.04.26 05:54
分类: Oracle tips
出处: http://jametong.itpub.net/post/5042/27712
---------------------------------------------------------------

1. 我自己想出来的方法.
思路.
a. 先确定元月1号是星期几. 这样可以退出第二周的第一天从哪天开始.
b. 计算出每个月第一天属于全年的那一周.
c. 这样就可以计算出具体某个日期属于哪一天了^_^.
SQL> @bbb.sql
SQL> select date_col,
2          to_number(to_char(trunc(sysdate,'yyyy'),'d')) year_week_day,
3          first_day_week,week_no,week_no - first_day_week + 1 week_no
4 from (
5     select date_col,
6         to_number(to_char(trunc(sysdate,'mm') + 7 - 1 ,'ww')) first_day_week,
7         to_number(to_char(date_col + 7 - 1,'ww')) week_no
8     from (
9     select trunc(sysdate,'mm') + rownum - 1 date_col
10     from dba_objects
11     where rownum <= to_number(to_char(last_day(sysdate),'dd'))
12    )
13 )
14 /

DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK    WEEK_NO    WEEK_NO
--------- ------------- -------------- ---------- ----------
01-APR-05             7             14         14          1
02-APR-05             7             14         14          1
03-APR-05             7             14         15          2
04-APR-05             7             14         15          2
05-APR-05             7             14         15          2
06-APR-05             7             14         15          2
07-APR-05             7             14         15          2
08-APR-05             7             14         15          2
09-APR-05             7             14         15          2
10-APR-05             7             14         16          3
11-APR-05             7             14         16          3

DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK    WEEK_NO    WEEK_NO
--------- ------------- -------------- ---------- ----------
12-APR-05             7             14         16          3
13-APR-05             7             14         16          3
14-APR-05             7             14         16          3
15-APR-05             7             14         16          3
16-APR-05             7             14         16          3
17-APR-05             7             14         17          4
18-APR-05             7             14         17          4
19-APR-05             7             14         17          4
20-APR-05             7             14         17          4
21-APR-05             7             14         17          4
22-APR-05             7             14         17          4

DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK    WEEK_NO    WEEK_NO
--------- ------------- -------------- ---------- ----------
23-APR-05             7             14         17          4
24-APR-05             7             14         18          5
25-APR-05             7             14         18          5
26-APR-05             7             14         18          5
27-APR-05             7             14         18          5
28-APR-05             7             14         18          5
29-APR-05             7             14         18          5
30-APR-05             7             14         18          5

30 rows selected.

SQL> spool off

2. Tom使用的办法. ( 不得不说这个方法比我的方法要简单多咯:-) )
思路:
a. 取出本月1号的前一个星期天的日期
b. 再取出与该星期天的星期的差距.

SQL> @bbb.sql
SQL> select x,next_day(trunc(x,'mm')-8,'sun') next_day,
2         trunc((x-next_day(trunc(x,'mm')-8,'sun'))/7)+1 "week"
3 from (
4    select trunc(sysdate,'mm') + rownum - 1 x
5    from dba_objects
6    where rownum <= to_number(to_char(last_day(sysdate),'dd'))
7 )
8 /

X         NEXT_DAY        week
--------- --------- ----------
01-APR-05 27-MAR-05          1
02-APR-05 27-MAR-05          1
03-APR-05 27-MAR-05          2
04-APR-05 27-MAR-05          2
05-APR-05 27-MAR-05          2
06-APR-05 27-MAR-05          2
07-APR-05 27-MAR-05          2
08-APR-05 27-MAR-05          2
09-APR-05 27-MAR-05          2
10-APR-05 27-MAR-05          3
11-APR-05 27-MAR-05          3

X         NEXT_DAY        week
--------- --------- ----------
12-APR-05 27-MAR-05          3
13-APR-05 27-MAR-05          3
14-APR-05 27-MAR-05          3
15-APR-05 27-MAR-05          3
16-APR-05 27-MAR-05          3
17-APR-05 27-MAR-05          4
18-APR-05 27-MAR-05          4
19-APR-05 27-MAR-05          4
20-APR-05 27-MAR-05          4
21-APR-05 27-MAR-05          4
22-APR-05 27-MAR-05          4

X         NEXT_DAY        week
--------- --------- ----------
23-APR-05 27-MAR-05          4
24-APR-05 27-MAR-05          5
25-APR-05 27-MAR-05          5
26-APR-05 27-MAR-05          5
27-APR-05 27-MAR-05          5
28-APR-05 27-MAR-05          5
29-APR-05 27-MAR-05          5
30-APR-05 27-MAR-05          5

30 rows selected.

SQL> spool off

jametong 发表于:2005.04.26 05:54 ::分类: ( Oracle tips ) ::阅读:(6738次) :: 评论 (201)

re: 如何确定今天是本月的第几周. [回复]

SELECT TO_CHAR(SYSDATE+1,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual

杨波评论于: 2007.03.10 11:31

gjXECP sucking the alice's nipples with a rings in them [回复]

gjXECP sucking the alice's nipples with a rings in them

Apocalypses 评论于: 2008.03.01 22:47

6fIRFU the ruler of bright moon [回复]

6fIRFU the ruler of bright moon

AqWEST 评论于: 2008.03.02 17:26

D7eaQu the current ruler of the kingdom of brightmoon [回复]

D7eaQu the current ruler of the kingdom of brightmoon

aric@us 评论于: 2008.03.03 11:53

re: 如何确定今天是本月的第几周. [回复]

Discount viagra online
viagra
https://www.blogger.com/comment.g?blogID=8290753909343683022&postID=7944080580789692023
[url=https://www.blogger.com/comment.g?blogID=8290753909343683022&postID=7944080580789692023]viagra[/url]

Viagra 评论于: 2008.04.30 18:37

re: 如何确定今天是本月的第几周. [回复]

Cheap cialis online
cialis
https://www.blogger.com/comment.g?blogID=8884434212950643172&postID=5916543911431987125
[url=https://www.blogger.com/comment.g?blogID=8884434212950643172&postID=5916543911431987125]cialis[/url]

Cialis 评论于: 2008.05.01 03:48

re: 如何确定今天是本月的第几周. [回复]

Cheap cialis online
cialis
https://www.blogger.com/comment.g?blogID=6982132927623912469&postID=483540205759878734
[url=https://www.blogger.com/comment.g?blogID=6982132927623912469&postID=483540205759878734]cialis[/url]

Cialis 评论于: 2008.05.11 10:03

re: 如何确定今天是本月的第几周. [回复]

buy viagra online
buy viagra
https://www.blogger.com/comment.g?blogID=7499722177443349493&postID=499806980012993493
[url=https://www.blogger.com/comment.g?blogID=7499722177443349493&postID=499806980012993493]buy viagra[/url]

buy viagra 评论于: 2008.05.13 08:48

re: 如何确定今天是本月的第几周. [回复]

Cheap cialis online
cialis
https://www.blogger.com/comment.g?blogID=3251352651752616462&postID=6079312996982939051
[url=https://www.blogger.com/comment.g?blogID=3251352651752616462&postID=6079312996982939051]cialis[/url]

Cialis 评论于: 2008.05.13 10:09

test [回复]

test

WEFGFWE 评论于: 2008.05.14 04:41

test [回复]

test

WEFGFWE 评论于: 2008.05.14 04:41

re: 如何确定今天是本月的第几周. [回复]

generic cialis online
generic cialis
https://www.blogger.com/comment.g?blogID=1658706046657025254&postID=6234864945622479675
[url=https://www.blogger.com/comment.g?blogID=1658706046657025254&postID=6234864945622479675]generic cialis[/url]

generic cialis 评论于: 2008.05.15 06:49

hluc cofslibrn [回复]

iepojln inysflec lwtf jxvhup vkednhaoy zapgn repjivs

mgrk fimyshweq 评论于: 2008.06.04 22:03

hluc cofslibrn [回复]

iepojln inysflec lwtf jxvhup vkednhaoy zapgn repjivs

mgrk fimyshweq 评论于: 2008.06.04 22:05

yufit vqefskixb [回复]

rdhcim wjhekmsvy fwrpchjbd pmser brxgtf bwkc pluk http://www.pksqev.kundgyet.com

sxrqzw qupzkwhmb 评论于: 2008.06.04 22:06

hxset ycmhitqre [回复]

ypiq yszqtokv yavpjieht oeyzganuh jnbar fcewpnjt xajhpcdg bgah bfiqwr