sql取出当前时间倒序30天做为一列

[huidesy]

得到的结果就有一列就从今天到上个月的今天

[Kav3000]

select DATEADD(DAY,-30,GETDATE())

[huidesy]

我要得到的一列就是
date
2012-08-04
2012-08-03
2012-08-02
2012-08-01
2012-07-31
2012-07-30
..........

[nanchangniat]

create function ShowDay()
returns @DataTable table(dDateTime datetime) 
as 
begin
    declare @num int
    select @num = 0
    while dateadd(month,-1,getdate()) <= dateadd(day,@num,getdate()) 
    begin
             insert into @DataTable(dDateTime)
             select dateadd(day,@num,getdate())
             select @num = @num - 1
    end
return 
end
go
select * from dbo.ShowDay()

/*--结果
2012-07-24 14:45:42.837
2012-07-23 14:45:42.837
2012-07-22 14:45:42.837
2012-07-21 14:45:42.837
2012-07-20 14:45:42.837
2012-07-19 14:45:42.837
2012-07-18 14:45:42.837
2012-07-17 14:45:42.837
2012-07-16 14:45:42.837
2012-07-15 14:45:42.837
2012-07-14 14:45:42.837
2012-07-13 14:45:42.837
2012-07-12 14:45:42.837
2012-07-11 14:45:42.837
2012-07-10 14:45:42.837
2012-07-09 14:45:42.837
2012-07-08 14:45:42.837
2012-07-07 14:45:42.837
2012-07-06 14:45:42.837
2012-07-05 14:45:42.837
2012-07-04 14:45:42.837
*/

[nanchangniat]

create function ShowDay()
returns @DataTable table(dDateTime datetime) 
as 
begin
    declare @num int
    select @num = 0
    while dateadd(month,-1,getdate()) <= dateadd(day,@num,getdate()) 
    begin
             insert into @DataTable(dDateTime)
             select dateadd(day,@num,getdate())
             select @num = @num - 1
    end
return 
end
go
select * from dbo.ShowDay()
/*--结果
2012-07-24 14:45:42.837
2012-07-23 14:45:42.837
2012-07-22 14:45:42.837
2012-07-21 14:45:42.837
2012-07-20 14:45:42.837
2012-07-19 14:45:42.837
2012-07-18 14:45:42.837
2012-07-17 14:45:42.837
2012-07-16 14:45:42.837
2012-07-15 14:45:42.837
2012-07-14 14:45:42.837
2012-07-13 14:45:42.837
2012-07-12 14:45:42.837
2012-07-11 14:45:42.837
2012-07-10 14:45:42.837
2012-07-09 14:45:42.837
2012-07-08 14:45:42.837
2012-07-07 14:45:42.837
2012-07-06 14:45:42.837
2012-07-05 14:45:42.837
2012-07-04 14:45:42.837
*/

[筱筱澄]

declare @a datetime 
set @a=getdate()
select [date]=dateadd(dd,number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,1,@a))
order by [date] desc

/*
date
-----------------------
2012-09-04 14:48:59.960
2012-09-03 14:48:59.960
2012-09-02 14:48:59.960
2012-09-01 14:48:59.960
2012-08-31 14:48:59.960
2012-08-30 14:48:59.960
2012-08-29 14:48:59.960
2012-08-28 14:48:59.960
2012-08-27 14:48:59.960
2012-08-26 14:48:59.960
2012-08-25 14:48:59.960
2012-08-24 14:48:59.960
2012-08-23 14:48:59.960
2012-08-22 14:48:59.960
2012-08-21 14:48:59.960
2012-08-20 14:48:59.960
2012-08-19 14:48:59.960
2012-08-18 14:48:59.960
2012-08-17 14:48:59.960
2012-08-16 14:48:59.960
2012-08-15 14:48:59.960
2012-08-14 14:48:59.960
2012-08-13 14:48:59.960
2012-08-12 14:48:59.960
2012-08-11 14:48:59.960
2012-08-10 14:48:59.960
2012-08-09 14:48:59.960
2012-08-08 14:48:59.960
2012-08-07 14:48:59.960
2012-08-06 14:48:59.960
2012-08-05 14:48:59.960
2012-08-04 14:48:59.960

(32 行受影响)


*/

[问苍天人生几何]

DECLARE @sql VARCHAR(MAX)
DECLARE @i INT
SET @i=30
WHILE @i>0
BEGIN
 IF @i=1
  SET @sql=ISNULL(@sql,'')+' SELECT DATEADD(DAY,0-CONVERT(INT,'+CONVERT(VARCHAR(2),@i)+'),GETDATE())'
 ELSE
  SET @sql=ISNULL(@sql,'')+' SELECT DATEADD(DAY,0-CONVERT(INT,'+CONVERT(VARCHAR(2),@i)+'),GETDATE()) UNION ALL '
 SET @i=@i-1
END
EXEC(@sql)

[xxjltan]

declare @tb table(n varchar(10))
declare @t datetime
select @t=getdate()
while (dateadd(month,-1,getdate())<=@t)
begin
insert into @tb select convert(varchar(10),@t,120)
set @t=dateadd(day,-1,@t)
end
select * from @tb

[以学习为目的]

declare @a datetime 
set @a=getdate()
select [date]=dateadd(dd,number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,-1,@a))
order by [date] desc

/*
date
-----------------------
2012-08-04 15:05:56.750
2012-08-03 15:05:56.750
2012-08-02 15:05:56.750
2012-08-01 15:05:56.750
2012-07-31 15:05:56.750
2012-07-30 15:05:56.750
2012-07-29 15:05:56.750
2012-07-28 15:05:56.750
2012-07-27 15:05:56.750
2012-07-26 15:05:56.750
2012-07-25 15:05:56.750
2012-07-24 15:05:56.750
2012-07-23 15:05:56.750
2012-07-22 15:05:56.750
2012-07-21 15:05:56.750
2012-07-20 15:05:56.750
2012-07-19 15:05:56.750
2012-07-18 15:05:56.750
2012-07-17 15:05:56.750
2012-07-16 15:05:56.750
2012-07-15 15:05:56.750
2012-07-15 15:05:56.750
2012-07-13 15:05:56.750
2012-07-12 15:05:56.750
2012-07-11 15:05:56.750
2012-07-10 15:05:56.750
2012-07-09 15:05:56.750
2012-07-07 15:05:56.750
2012-07-07 15:05:56.750
2012-07-06 15:05:56.750
2012-07-05 15:05:56.750
2012-07-04 15:05:56.750
(32 行受影响)


*/

[walkman_22]

感觉要求没写清楚。

[问苍天人生几何]

[Quote=引用 8 楼 的回复:]
SQL code


declare @a datetime
set @a=getdate()
select [date]=dateadd(dd,number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,-1,@a))
order by [……
[/Quote]
你的'-'加错地方了，但奇怪你的结果怎么又没问题？

declare @a datetime 
set @a=getdate()
select [date]=dateadd(dd,-number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,1,@a))
order by [date] desc

[lichengduo]

declare @a datetime
set @a=getdate()
select [date]=dateadd(dd,number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,1,@a))
order by [date] desc

----------
2012-09-06
2012-09-05
2012-09-04
2012-09-03
2012-09-02
2012-09-01
2012-08-31
2012-08-30
2012-08-29
2012-08-28
2012-08-27
2012-08-26
2012-08-25
2012-08-24
2012-08-23
2012-08-22
2012-08-21
2012-08-20
2012-08-19
2012-08-18
2012-08-17
2012-08-16
2012-08-15
2012-08-14
2012-08-13
2012-08-12
2012-08-11
2012-08-10
2012-08-09
2012-08-08
2012-08-07
2012-08-06

[lichengduo]

[Quote=引用 11 楼 的回复:]
declare @a datetime
set @a=getdate()
select [date]=dateadd(dd,number,@a) from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,1,@a))
order by [date] desc

-……
[/Quote]

declare @a datetime
set @a=getdate()
select
[date]=convert(varchar(10),dateadd(dd,number,@a),120)
from master..spt_values where type='p'
and number between 0 and datediff(dd,@a,dateadd(mm,1,@a))
order by [date] desc