求一SQL语句，在线等

Question

现需要在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

Answer 1

备注下，是oracle数据库，谢谢

Answer 2

写一个mssql给点思路也行，谢谢

Answer 3

说一下您的目的先吧,可能可以用ORACLE的开窗函数来解决您的问题

Answer 4

您是不是要统计近来一个月的数据吧??连续一个月的聚合等?这样的话,就适合用ORACLE窗口函数来解决了

Answer 5

MSSQL:
datediff(dd,[col1],getdate())>0 And datediff(dd,[col1],getdate())<30

Answer 6

----ms-sql
declare @startdate datetime,@enddate datetime
set @startdate='2009-09-28'
set @enddate='2009-10-5'select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>0 
    and type='p'
/*----------
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05(7 行受影响)
*/

Answer 7

DECLARE @DAY INT
SET @DAY=DATEDIFF(DD,GETDATE(),DATEADD(MM,1,GETDATE()))
SELECT CONVERT(VARCHAR(10),DATEADD(DD,-NUMBER,GETDATE()),120)AS TIME
FROM MASTER..SPT_VALUES WHERE TYPE='P' AND 
CONVERT(VARCHAR(10),DATEADD(DD,-NUMBER,GETDATE()),120)>=
CONVERT(VARCHAR(10),DATEADD(DD,-@DAY,GETDATE()),120)TIME       
---------- 
2010-01-15
2010-01-14
2010-01-13
2010-01-12
2010-01-11
2010-01-10
2010-01-09
2010-01-08
2010-01-07
2010-01-06
2010-01-05
2010-01-04
2010-01-03
2010-01-02
2010-01-01
2009-12-31
2009-12-30
2009-12-29
2009-12-28
2009-12-27
2009-12-26
2009-12-25
2009-12-24
2009-12-23
2009-12-22
2009-12-21
2009-12-20
2009-12-19
2009-12-18
2009-12-17
2009-12-16
2009-12-15（所影响的行数为 32 行）SQL的

Answer 8

DECLARE @DAY INT
SET @DAY=DATEDIFF(DD,GETDATE(),DATEADD(MM,1,GETDATE()))SELECT 
CONVERT(VARCHAR(10),DATEADD(DD,-NUMBER,GETDATE()),120)AS TIME
FROM 
MASTER..SPT_VALUES 
WHERE TYPE='P' 
AND CONVERT(VARCHAR(10),DATEADD(DD,-NUMBER,GETDATE()),120)
>=CONVERT(VARCHAR(10),DATEADD(DD,-@DAY,GETDATE()),120) ORDER BY NUMBER DESC/*
TIME       
---------- 
2009-12-15
2009-12-16
2009-12-17
2009-12-18
2009-12-19
2009-12-20
2009-12-21
2009-12-22
2009-12-23
2009-12-24
2009-12-25
2009-12-26
2009-12-27
2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13
2010-01-14
2010-01-15（所影响的行数为 32 行）加个排序

Answer 9

select rownum as nowid,to_char(add_months(sysdate, -1) + rownum - 1 ,'yyyy-mm-dd') as newDate
from dual connect by  rownum <= sysdate - add_months(sysdate, -1)select trunc(add_months(sysdate, -1)+rownum-1)
 from all_tables
  where add_months(sysdate, -1)+rownum-1<=sysdate;

Answer 10

我知道在SqlServer中使用when then