请教一SQL
name bdate edate
tom 2007-7-1 2007-7-4
想得到
tom 2007-7-1
tom 2007-7-2
tom 2007-7-3
tom 2007-7-4
[解决办法]
Create Table TEST
(name Varchar(10),
bdate DateTime,
edate DateTime)
Insert TEST Select 'tom ', '2007-7-1 ', '2007-7-4 '
GO
Select TOP 1000 ID = Identity(Int, 0, 1) Into #T From SysColumns A, SysObjects
Select
A.name,
Convert(Varchar(10), DateAdd(dd, B.ID, A.bdate), 120) As [date]
From
TEST A
Inner Join
#T B
On DateAdd(dd, B.ID, A.bdate) <= A.edate
Drop Table #T
GO
Drop Table TEST
--REsult
/*
namedate
tom2007-07-01
tom2007-07-02
tom2007-07-03
tom2007-07-04
*/
[解决办法]
declare @a table(name varchar(10), bdate smalldatetime, edate smalldatetime)
insert @a select 'tom ', '2007-7-1 ', '2007-7-4 '
select top 31 id=identity(int,0,1) into # from syscolumns
select * from
(select name,x=case when dateadd(day,id,bdate) <=edate then convert(varchar(10),dateadd(day,id,bdate),120) else ' ' end from @a a,# b) aa
where x <> ' '
drop table #
