由日期期间返回具体的日期

[sunylf]

有个表
BeginDate EndDate
2009-1-25 2009-1-28
如何获得
2009-1-25
2009-1-26
2009-1-27
2009-1-28的记录啊

最好能用一条语句实现,sqlsever 2000.

[-狙击手-]

------------------------------------
-- Author:  happyflsytone  
-- Date:2008-11-17 17:32:45
------------------------------------

-- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL 
    DROP TABLE TA
Go
CREATE TABLE TA(BeginDate SMALLDATETIME,EndDate SMALLDATETIME)
Go
INSERT INTO TA
 SELECT '2009-1-25','2009-1-28' 
GO
--Start
SELECT 
	dateadd(d,n, BeginDate) as d
FROM 
	TA a,
(select 0 as n union all select 1 union select 2 union all select 3 union all select 4) b
where dateadd(d,n, BeginDate) <= EndDate

--Result:
/*

d
-----------------------
2009-01-25 00:00:00
2009-01-26 00:00:00
2009-01-27 00:00:00
2009-01-28 00:00:00

(4 行受影响)
*/
--End 

[ws_hgo]

sf

[ws_hgo]

sf

[fcuandy]

一条语句不可能。

时间间隔大了，你写union啊。

[-狙击手-]

select 0 as n union all select 1 union select 2 union all select 3 union all select 4

－－
如果时间跨度大，就把这个接长点

[ws_hgo]

等下

[子陌红尘]

declare @t table(BeginDate datetime,EndDate datetime)
insert into @t select '2009-1-25','2009-1-28'

select
    dateadd(dd,b.id,a.BeginDate) as date
from
    @t a,
    (select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b
where
    b.id<=datediff(dd,BeginDate,EndDate)

/*
date                                                   
------------------------------------------------------ 
2009-01-25 00:00:00.000
2009-01-26 00:00:00.000
2009-01-27 00:00:00.000
2009-01-28 00:00:00.000
*/

[-狙击手-]

[Quote=引用 4 楼 fcuandy 的回复:]
一条语句不可能。

时间间隔大了，你写union啊。
[/Quote]
那可以两个union 到10的子集　进行乘运算

[fcuandy]

union再cross join,或连几个系统表，我宁原写循环,效率好多了。

[dobear_0922]

[Quote=引用 7 楼 libin_ftsafe 的回复:]

declare @t table(BeginDate datetime,EndDate datetime)
insert into @t select '2009-1-25','2009-1-28'

select
    dateadd(dd,b.id,a.BeginDate) as date
from
    @t a,
    (select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b
where
    b.id<=datediff(dd,BeginDate,EndDate)

/*
date                                                   
------------------------------------------------------ 
2009-01-25 00:00:00.000
2009-01-26 00:00:00.000
2009-01-27 00:00:00.000
2009-01-28 00:00:00.000
*/

[/Quote]
顶钻钻，，，

[fcuandy]

而且跨度很大的话，连两三次系统表记录也未必够，连多了根本不敢跑..

[-狙击手-]

select a.n+10*b.n as n
from (select 0 as n union all select 1  union all select 2  
union all select 3  union all select 4  union all select 5  
union all select 6  union all select 7  union all select 8  union all select 9 ) a
,(select 0 as n union all select 1  union all select 2  
union all select 3  union all select 4  union all select 5  
union all select 6  union all select 7  union all select 8  union all select 9 ) b

[fcuandy]

楼主最初问过，问的是

。。。
2007-1-1
2007-1-2
。。。
2007-12-31
2008-1-1
。。。
2008-12-31
2009-1-1
。。。
2009-12-31

群里那个是你吧

[-狙击手-]

[Quote=引用 11 楼 fcuandy 的回复:]
而且跨度很大的话，连两三次系统表记录也未必够，连多了根本不敢跑..
[/Quote]

呵呵，那当然　

[butchroller]

select rn = identity(int, 0, 1) into #t from sysobjects;
select m.BeginDate + t.rn from #t t, mytab m where m.BeginDate + t.rn <= m.EndDate;
drop table #t;

[fcuandy]

不过说实话，楼主问时，我第一个想到的是select identity ... into ..然后dateadd,或者是union再join,
压根没想到count计数..脑子还是不够快。

钻钻要顶。

[csdyyr]

又学了一招

[ws_hgo]

create table FFFF
(
   tdate datetime
)
declare @i int
set @i=(select datediff(dd,'2009-1-25','2009-1-28'))
select dateadd(dd,1,'2009-1-25')
while @i>0
begin
  insert into FFFF select dateadd(dd,@i-1,'2009-1-25')
  set @i=@i-1
end
select * from FFFF

[ws_hgo]

if object_id('FFFF') is not null drop table FFFF
create table FFFF
(
   tdate datetime
)
declare @i int
set @i=(select datediff(dd,'2009-1-25','2009-1-28'))
select dateadd(dd,1,'2009-1-25')
while @i>0
begin
  
  insert into FFFF select dateadd(dd,@i-1,'2009-1-25')
  set @i=@i-1
  if(@i=1)
  begin
  insert into FFFF select '2009-1-28'
  end
end
select * from FFFF