SQL語句求問：已知起止日期怎樣列出此范圍內的所有日期
如：起始日期：2007/07/01 終止日期：2007/07/05
結果：
2007/07/01
2007/07/02
2007/07/03
2007/07/04
2007/07/05
------解决方案--------------------
set nocount on
declare @dtBegin datetime
declare @dtEnd datetime
declare @t table(dt datetime)
select @dtBegin= '2006-7-1 '
select @dtEnd= '2006-7-5 '
while @dtBegin <[email protected]
begin
insert @t select @dtBegin
set @[email protected]+1
end
select time=convert(char(10),dt,120) from @t
------解决方案--------------------
不用循环
declare @begindate datetime,@enddate datetime,@sql varchar(1000)
select @begindate= '2007-07-01 ',@enddate= '2007-07-05 '
set @sql= 'select top '+rtrim(datediff(day,@begindate,@enddate)+1)+ ' id=identity(int,0,1) into t_date from sysobjects,syscolumns '
exec(@sql)
select dateadd(day,id,@begindate) From t_date
drop table t_date
------解决方案--------------------
declare @startdate datetime
declare @enddate datetime
set @startdate= '2007/07/01 '
set @enddate= '2007/07/05 '
select @startdate+d [time]
from
(select top (datediff(day,@startdate,@enddate)+1) row_number() over(order by getdate())-1 d from sys.objects) t