大家好,请教下日期间隔的问题,需求是这样的假设我有两个日期比如是:2008-07,2009-05,这两个值都是varchar2型,能否用一条SQL语句得出这两个日期间隔的日期呢?返回结果如下所示:2008-08
2008-09
2008-10
2008-11
2008-12
2009-01
2009-02
2009-03
2009-04十分感谢!
2008-09
2008-10
2008-11
2008-12
2009-01
2009-02
2009-03
2009-04十分感谢!
SQL> ed
已写入 file afiedt.buf 1 CREATE TABLE test(
2 col VARCHAR2(15)
3* )
SQL> /表已创建。SQL> ed
已写入 file afiedt.buf 1* INSERT INTO test VALUES('2001-01')
SQL> /已创建 1 行。SQL> ed
已写入 file afiedt.buf 1* INSERT INTO test VALUES('2005-01')
SQL> /已创建 1 行。SQL> ed
已写入 file afiedt.buf 1* INSERT INTO test VALUES('2008-09')
SQL> /已创建 1 行。SQL> ed
已写入 file afiedt.buf 1* INSERT INTO test VALUES('2028-09')
SQL> /已创建 1 行。SQL> select * from test;COL
---------------
2001-01
2005-01
2008-09
2028-09SQL> ed
已写入 file afiedt.buf 1 SELECT * FROM test
2* WHERE to_date(col,'yyyy-mm') BETWEEN to_date('2008-07','yyyy-mm') AND to_d
te('2009-05','yyyy-mm')
SQL> /COL
---------------
2008-09
'yyyy-mm')
result
FROM DUAL
CONNECT BY LEVEL <=
(SELECT MONTHS_BETWEEN (TO_DATE ('2009-05', 'yyyy-mm'),
TO_DATE ('2008-07', 'yyyy-mm'))
FROM DUAL)
select to_char(add_months(to_date('2008-07','YYYY/MM'),rownum),'YYYY-MM')
from dual
connect by rownum < months_between(to_date('2009-05','YYYY/MM'),to_date('2008-07','YYYY/MM'))