如题所示，输入“2007-10-01”，“2007-10-05”
得到的结果是：
2007-10-01
2007-10-02
2007-10-03
2007-10-04
2007-10-05
谢谢！
------解决方案--------------------
写法都在上面了.
------解决方案--------------------
create function getDate(@s smalldatetime,@e smalldatetime)
returns @a table(d smalldatetime)
as
begin
declare @t table(id int identity(0,1),x int)
insert @t select top 100 1 from syscolumns
insert @a select dateadd(day,id,@s) from @t where dateadd(day,id,@s)<[email protected]
return
end
------解决方案--------------------
- SQL code
DECLARE @sd DATETIME,@ed DATETIME,@k INTSELECT @sd='2007-10-01',@ed='2007-10-7',@k=DATEDIFF(dd,@sd,@ed)-1DECLARE @i INT,@s VARCHAR(8000)SELECT @i=0,@s=''SET ROWCOUNT @kSELECT @[email protected]+1,@[email protected]+' UNION SELECT d=''' + CONVERT(VARCHAR(10),DATEADD(dd,@i,@sd),120) + '''' FROM syscolumns,sysobjectsSELECT @s=STUFF(@s,1,6,'')EXEC(@s)SET ROWCOUNT 0
------解决方案--------------------
- SQL code
--tryalter function fn( @begdate datetime, @enddate datetime)returns @t table(ddate datetime)asbegin while(datediff(d,@begdate,@enddate)>0) begin insert @t(ddate) values(@begdate) set @begdate=dateadd(d,1,@begdate) end insert @t(ddate) values(@begdate) return endselect * from dbo.fn('2007-10-01','2007-10-05')
------解决方案--------------------
- SQL code
alter function wsp2(@stardate datetime,@enddate datetime)returns @t table(dt datetime)asbegin declare @n int set @n=datediff(dd,@stardate,@enddate) if(@n>0) begin while(@n>0) begin insert into @t select dateadd(dd,@n-1,@stardate) set @[email protected] end end return endselect * FROM dbo.wsp2('2007-12-1','2007-12-5')
------解决方案--------------------
- SQL code
--如果是2000，要写成函数，只能用循环：create function fn_date(@1 datetime,@2 datetime)returns @Return table (Date datetime)asbegin[email protected]<@1，交换。if @1>@2 select @[email protected][email protected],@2=@1-@2,@1=@1-@2declare @i intset @i=datediff(day,-1,@1)while @i<datediff(day,0,@2)begin insert @Return values(@i) set @[email protected]+1endreturnendgoselect * from dbo.fn_date('2007-10-07','2007-10-01')/*Date -----------------------2007-10-02 00:00:00.0002007-10-03 00:00:00.0002007-10-04 00:00:00.0002007-10-05 00:00:00.0002007-10-06 00:00:00.000*/--删除drop function fn_date
------解决方案--------------------
- SQL code
declare @begin datetime,@end datetime,@i intset @begin='2007-10-01'set @end='2007-10-05'set @i=1while @i<=datediff(d,@begin,@end)beginPrint convert(varchar(10),dateadd(dd,@i,@begin),120)set @[email protected]+1end2007-10-022007-10-032007-10-042007-10-05
------解决方案--------------------
- SQL code
DECLARE @sd DATETIME,@ed DATETIME,@k INTSELECT @sd='2007-10-01',@ed='2007-10-7',@k=DATEDIFF(dd,@sd,@ed)-1DECLARE @i INT,@s VARCHAR(8000)SELECT @i=0,@s=''SELECT @[email protected]+1,@[email protected]+ CASE WHEN @i<[email protected] THEN ' UNION SELECT d=''' + CONVERT(VARCHAR(10),DATEADD(dd,@i,@sd),120) + '''' ELSE '' END FROM syscolumns,sysobjectsSELECT @s=STUFF(@s,1,6,'')EXEC(@s)