oracle时间问题，谢谢

[sqllong]

现需要在oracle中通过sql语句构造最近1一个月的日期。结果如下：
id colDate
1 2009-12-15
2 2009-12-16
3 2009-12-17
4 2009-12-18
........
30 2010-01-14
31 2010-01-15

[shiyiwan]

select rownum,add_months(sysdate, -1) + rownum - 1
from dual
where rownum <= sysdate - add_months(sysdate, -1);

[shiyiwan]

where改成connect by
长期不答题手生了

[ACMAIN_CHM]

SQL> select trunc(add_months(sysdate, -1)+rownum-1)
  2  from all_tables
  3  where add_months(sysdate, -1)+rownum-1<=sysdate;

TRUNC(ADD
---------
15-DEC-09
16-DEC-09
17-DEC-09
18-DEC-09
19-DEC-09
20-DEC-09
21-DEC-09
22-DEC-09
23-DEC-09
24-DEC-09
25-DEC-09

TRUNC(ADD
---------
26-DEC-09
27-DEC-09
28-DEC-09
29-DEC-09
30-DEC-09
31-DEC-09
01-JAN-10
02-JAN-10
03-JAN-10
04-JAN-10
05-JAN-10

TRUNC(ADD
---------
06-JAN-10
07-JAN-10
08-JAN-10
09-JAN-10
10-JAN-10
11-JAN-10
12-JAN-10
13-JAN-10
14-JAN-10
15-JAN-10

32 rows selected.

SQL>

[iqlife]

select  to_char(minutes,'yyyy-mm-dd  hh24:mi')  from 
(
select to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss') + (rownum-1)/24/60 as minutes
from dual
connect by rownum <= (to_date('2009-10-13 12:12:12','yyyy-mm-dd hh24:mi:ss') -
to_date('2009-10-12 12:12:12','yyyy-mm-dd hh24:mi:ss')) * 24 * 60 
) a

希望对你有帮助

[sqllong]

多谢，结贴