求一函数，输入两个日期，得到这两个日期间的所有日期。该如何处理

求一函数，输入两个日期，得到这两个日期间的所有日期。
如题所示，输入“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)<=@e
return
end

------解决方案--------------------

SQL code

DECLARE @sd DATETIME,@ed DATETIME,@k INT
SELECT @sd='2007-10-01',@ed='2007-10-7',@k=DATEDIFF(dd,@sd,@ed)-1
DECLARE @i INT,@s VARCHAR(8000)
SELECT @i=0,@s=''
SET ROWCOUNT @k
SELECT @i=@i+1,@s=@s+' UNION SELECT d=''' + CONVERT(VARCHAR(10),DATEADD(dd,@i,@sd),120) + '''' FROM syscolumns,sysobjects
SELECT @s=STUFF(@s,1,6,'')
EXEC(@s)
SET ROWCOUNT 0

------解决方案--------------------

SQL code


--try


alter function fn(
    @begdate datetime,
    @enddate datetime
)
returns @t table(ddate datetime)
as
begin
    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  
end

select * from dbo.fn('2007-10-01','2007-10-05')

------解决方案--------------------

SQL code


alter function wsp2(@stardate datetime,@enddate datetime)
returns @t table(dt datetime)
as
begin
    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 @n=@n-1
        end
    end
    return 
end

select * 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)
as
begin
--如果@2<@1，交换。
if @1>@2
    select @1=@1+@2,@2=@1-@2,@1=@1-@2
declare @i int
set @i=datediff(day,-1,@1)
while @i<datediff(day,0,@2)
begin
    insert @Return values(@i)
    set @i=@i+1
end
return
end
go

select * from dbo.fn_date('2007-10-07','2007-10-01')
/*
Date                                                   
-----------------------
2007-10-02 00:00:00.000
2007-10-03 00:00:00.000
2007-10-04 00:00:00.000
2007-10-05 00:00:00.000
2007-10-06 00:00:00.000
*/

--删除
drop function fn_date

------解决方案--------------------

SQL code



declare @begin datetime,@end datetime,@i int
set @begin='2007-10-01'
set @end='2007-10-05'


set @i=1
while @i<=datediff(d,@begin,@end)
begin
Print convert(varchar(10),dateadd(dd,@i,@begin),120)
set @i=@I+1
end

2007-10-02
2007-10-03
2007-10-04
2007-10-05

------解决方案--------------------

SQL code

DECLARE @sd DATETIME,@ed DATETIME,@k INT
SELECT @sd='2007-10-01',@ed='2007-10-7',@k=DATEDIFF(dd,@sd,@ed)-1
DECLARE @i INT,@s VARCHAR(8000)
SELECT @i=0,@s=''
SELECT @i=@i+1,@s=@s+ CASE WHEN @i<=@k THEN ' UNION SELECT d=''' + CONVERT(VARCHAR(10),DATEADD(dd,@i,@sd),120) + '''' ELSE '' END FROM syscolumns,sysobjects
SELECT @s=STUFF(@s,1,6,'')
EXEC(@s)

求一函数，输入两个日期，得到这两个日期间的所有日期。该如何处理

相关推荐