给出开始时间(beg_date)和结束时间(end_date),如何得到这段时间内每个月末的日期,每个周末的日期。
如BEG_DATE='2008-03-22' END_DATE='2008-06-11'
现需要得到这段日期内每个月末日期序列:
2008-03-31
2008-04-30
2008-05-31
还需得到这段日期内每个周末(周五)日期序列:2008-03-28
2008-04-04
2008-04-11
2008-04-18
2008-04-25
。
。
。
2008-05-30
2008-06-06注:假定有那么一张自然日历的表
如BEG_DATE='2008-03-22' END_DATE='2008-06-11'
现需要得到这段日期内每个月末日期序列:
2008-03-31
2008-04-30
2008-05-31
还需得到这段日期内每个周末(周五)日期序列:2008-03-28
2008-04-04
2008-04-11
2008-04-18
2008-04-25
。
。
。
2008-05-30
2008-06-06注:假定有那么一张自然日历的表
select date from tb where to_char(date +1,'dd') =1;
当前星期的数值为6就是周五,类似
select date from tb where to_char(date ,'D') =6;
取本月的最后一天
别忘替换开始和结束时间。
select *
from (
select to_date('2007-01-22', 'yyyy-mm-dd') + rownum as DATA_LIST
from dual
connect by rownum <= (to_date('2008-03-22', 'yyyy-mm-dd') -
to_date('2007-01-22', 'yyyy-mm-dd'))) A
where A.DATA_LIST = last_day(a.DATA_LIST)
or to_char(A.DATA_LIST,'d') = 6
忽然想起来,
少了个开始时间,再试一下。
select *
from (
select to_date('2007-01-22', 'yyyy-mm-dd') + rownum - 1 as DATA_LIST
from dual
connect by rownum <= (to_date('2008-03-22', 'yyyy-mm-dd') -
to_date('2007-01-22', 'yyyy-mm-dd'))) A
where A.DATA_LIST = last_day(a.DATA_LIST)
or to_char(A.DATA_LIST,'d') = 6
FROM DUAL
CONNECT BY LEVEL <=
DECODE (TRUNC (SYSDATE),
TRUNC (LAST_DAY (SYSDATE)), TRUNC
(MONTHS_BETWEEN (SYSDATE,
SYSDATE
- 100
)
) + 1,
TRUNC (MONTHS_BETWEEN (SYSDATE, SYSDATE - 100))
)
结果
Row# lastday1 2008/2/29
2 2008/3/31
3 2008/4/30把里面的sysdate换成你的end_date,sysdate-100换成beg_date 就能出结果了
其中如果end_date是当月的最后一天,就会连end_date也输出,纪录也会多一条
比如我把上面的sysdate换成sysdate+17(5.31日)
结果就是
Row# lastday1 2008/2/29
2 2008/3/31
3 2008/4/30
4 2008/5/31
SELECT DECODE (TO_CHAR (SYSDATE - 97, 'd'),
'5', TRUNC (SYSDATE - 97),
NEXT_DAY (TRUNC (SYSDATE - 97), 5)
)
+ (ROWNUM - 1) * 7
FROM DUAL
CONNECT BY ROWNUM <
DECODE (TO_CHAR (SYSDATE - 97, 'yyyy'),
TO_CHAR (SYSDATE + 2, 'yyyy'), TO_NUMBER
(TO_CHAR (SYSDATE
+ 2,
'iw'
)
)
- TO_NUMBER (TO_CHAR (SYSDATE - 97, 'iw')),
52
- TO_NUMBER ( TO_CHAR (SYSDATE - 97, 'iw')
+ 52
* ( TO_NUMBER (TO_CHAR (SYSDATE + 2,
'yyyy'
)
)
- TO_NUMBER (TO_CHAR (SYSDATE - 97,
'yyyy'
)
)
)
+ TO_NUMBER (TO_CHAR (SYSDATE + 2,
'yyyy')
)
)
)
+ DECODE (TO_CHAR (SYSDATE+3, 'd'), '6', 2,'7',2,'1',2, 1)两个日期之间的周末
我这里的日期是从2.7(周五)到5.15(周五)
结果
Row# DECODE(TO_CHAR(SYSDATE-97,'D'),'5',TRUNC(SYSDATE-97),NEXT_DAY(TRUNC(SYSDATE-97),5))+(ROWNUM-1)*71 2008/2/7
2 2008/2/14
3 2008/2/21
4 2008/2/28
5 2008/3/6
6 2008/3/13
7 2008/3/20
8 2008/3/27
9 2008/4/3
10 2008/4/10
11 2008/4/17
12 2008/4/24
13 2008/5/1
14 2008/5/8
15 2008/5/15
把日期换成sysdate-100(2.4),sysdate
结果
Row# DECODE(TO_CHAR(SYSDATE-100,'D'),'5',TRUNC(SYSDATE-100),NEXT_DAY(TRUNC(SYSDATE-100),5))+(ROWNUM-1)*71 2008/2/7
2 2008/2/14
3 2008/2/21
4 2008/2/28
5 2008/3/6
6 2008/3/13
7 2008/3/20
8 2008/3/27
9 2008/4/3
10 2008/4/10
11 2008/4/17
12 2008/4/24
13 2008/5/1
14 2008/5/8同样,你把sysdate+2换成end_date,sysdate-97换成 beg_date
'5', TRUNC (SYSDATE - 203),
NEXT_DAY (TRUNC (SYSDATE - 203), 5)
)
+ (ROWNUM - 1) * 7
FROM DUAL
CONNECT BY ROWNUM <
DECODE (TO_CHAR (SYSDATE - 203, 'yyyy'),
TO_CHAR (SYSDATE, 'yyyy'), TO_NUMBER
(TO_CHAR (SYSDATE,
'iw'
)
)
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'iw')),
( 52
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'iw'))
+ 52
* ( TO_NUMBER (TO_CHAR (SYSDATE, 'yyyy'))
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'yyyy'))
- 1
)
+ TO_NUMBER (TO_CHAR (SYSDATE, 'iw'))
)
)
+ DECODE (TO_CHAR (SYSDATE, 'd'), '6', 2, '7', 2, '1', 2, 1)
结果Row# DECODE(TO_CHAR(SYSDATE-203,'D'),'5',TRUNC(SYSDATE-203),NEXT_DAY(TRUNC(SYSDATE-203),5))+(ROWNUM-1)*71 2007/10/25
2 2007/11/1
3 2007/11/8
4 2007/11/15
5 2007/11/22
6 2007/11/29
7 2007/12/6
8 2007/12/13
9 2007/12/20
10 2007/12/27
11 2008/1/3
12 2008/1/10
13 2008/1/17
14 2008/1/24
15 2008/1/31
16 2008/2/7
17 2008/2/14
18 2008/2/21
19 2008/2/28
20 2008/3/6
21 2008/3/13
22 2008/3/20
23 2008/3/27
24 2008/4/3
25 2008/4/10
26 2008/4/17
27 2008/4/24
28 2008/5/1
29 2008/5/8
应该是SELECT DECODE (TO_CHAR (SYSDATE - 203, 'd'),
'6', TRUNC (SYSDATE - 203),
NEXT_DAY (TRUNC (SYSDATE - 203), 6)
)
+ (ROWNUM - 1) * 7
FROM DUAL
CONNECT BY ROWNUM <
DECODE (TO_CHAR (SYSDATE - 203, 'yyyy'),
TO_CHAR (SYSDATE, 'yyyy'), TO_NUMBER
(TO_CHAR (SYSDATE,
'iw'
)
)
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'iw')),
( 52
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'iw'))
+ 52
* ( TO_NUMBER (TO_CHAR (SYSDATE, 'yyyy'))
- TO_NUMBER (TO_CHAR (SYSDATE - 203, 'yyyy'))
- 1
)
+ TO_NUMBER (TO_CHAR (SYSDATE, 'iw'))
)
)
+ DECODE (TO_CHAR (SYSDATE, 'd'), '6', 2, '7', 2, '1', 2, 1)结果
Row# DECODE(TO_CHAR(SYSDATE-203,'D'),'6',TRUNC(SYSDATE-203),NEXT_DAY(TRUNC(SYSDATE-203),6))+(ROWNUM-1)*71 2007/10/26
2 2007/11/2
3 2007/11/9
4 2007/11/16
5 2007/11/23
6 2007/11/30
7 2007/12/7
8 2007/12/14
9 2007/12/21
10 2007/12/28
11 2008/1/4
12 2008/1/11
13 2008/1/18
14 2008/1/25
15 2008/2/1
16 2008/2/8
17 2008/2/15
18 2008/2/22
19 2008/2/29
20 2008/3/7
21 2008/3/14
22 2008/3/21
23 2008/3/28
24 2008/4/4
25 2008/4/11
26 2008/4/18
27 2008/4/25
28 2008/5/2
29 2008/5/9select sysdate,to_char(sysdate,'d'),sysdate-203,to_char(sysdate-203,'d') from dual
Row# SYSDATE TO_CHAR(SYSDATE,'D') SYSDATE-203 TO_CHAR(SYSDATE-203,'D')1 2008/5/14 22:27:35 4 2007/10/24 22:27:35 4
假设自然日历的表格test_rq 是有两列 rq date,rqn number
例如数据是这样的20080101(date),20080101(number) ,
下例就是查询20080501到20081001之间的月末序列和周末序列.
[code=SQL]--查询月末的
select rq from test_rq where to_char(rq,'yyyymm')<>to_char(rq+1,'yyyymm')
and (rqn between 20080501 and 20081001)
--查询周末的
select rq from test_rq where to_char(rq,'d')=6 and (rqn between 20080501 and 20081001)[/code]如果需要常常查询这个表格,建议表格可以多建一些列.
譬如
rq date,
rqn number,
年
月
日
第几个星期
星期几
是否月末
..
加上索引,在这样的小表上一定是飞快的。