怎样返回时间段的所有日期

[typecode]

怎样返回时间段的所有日期 ？
例如：开如日期：2004-01-01
结束日期：2004-01-05
返回结果集：2004-01-01
2004-01-02
2004-01-03
2004-01-04
2004-01-05

[lsxaa]

select 开始日期+b.id
from (select 1 as id union all
select 2 as id union all
...
select 30 as id union all
)b
where 开始日期+b.id <=结束日期

[lsxaa]

有点小错误 ,改

select 开始日期+b.id
from (select 1 as id union all
select 2 as id union all
...
select 30 as id )b
where 开始日期+b.id <=结束日期

[txlicenhe]

select dateadd(day,id,'2004-01-01') from (
select id=a.id+b.id*10+c.id*100+d.id*1000 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <5 -- 此处id 最大不能超过10000
order by id

[txlicenhe]

select dateadd(day,id,开始日期) from (
select id=a.id+b.id*10+c.id*100+d.id*1000 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where dateadd(day,id,开始日期) <= 结束日期
order by id

[lsxaa]

或者

select id=(identity(int,1,1)) into #t from syscolumns a,syscolumns b,syscolumns c

select 开始日期+b.id
from #t b
where 开始日期+b.id <=结束日期

[子陌红尘]

create table #period(
date datetime
)

insert into #period select cast('2004-01-01' as datetime)

while (SELECT max(date) FROM #period) < cast('2004-01-05' as datetime)
begin
insert into #period select max(date)+1 from #period
end

select convert(varchar(10),date,120) from #period

[子陌红尘]

create table #period(
date datetime
)

insert into #period select cast('2004-01-01' as datetime)

while (SELECT max(date) FROM #period) < cast('2004-01-05' as datetime)
begin
insert into #period select max(date)+1 from #period
end

select convert(varchar(10),date,120) from #period

[davorsuker39]

select id=(identity(int,1,1)) into #t from syscolumns a,syscolumns b,syscolumns c

select 开始日期+b.id
from #t b
where 开始日期+b.id <=结束日期

小刀这个简化一点

[netspies]

没有这么复杂吧！

[zhang_yzy]

用查询语句实现起来很麻烦的,
最好用存储过程写,会简单的多

[playyuer]

T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx

[dlpseeyou]

改改!!!
select convert(varchar(10),dateadd(day,id,'2003-05-06'),120) from (
select id=a.id+b.id*10+c.id*100+d.id*1000 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where dateadd(day,id,'2003-05-06') <= '2004-02-02'
order by id