SQl 函数实现返回时间列表

create FUNCTION [dbo].[GetDates]
(
    @startdate       varchar(100),  
    @enddate           varchar(100),
    @daycount int --上面两个时间的间隔天数
)
RETURNS @date table(
    dates varchar(10)
 )   
AS

BEGIN
declare @start_year int,
            @start_month int,
            @start_day int,
            @end_year int,
            @end_month int,
            @end_day int
    select @start_year = cast(SUBSTRING(@startdate,1,4)as int),@start_month = cast(SUBSTRING(@startdate,6,2)as int),@start_day = cast(SUBSTRING(@startdate,9,2)as int),@end_year = cast(SUBSTRING(@enddate,1,4)as int),@end_month =  cast(SUBSTRING(@enddate,6,2)as int),@end_day =  cast(SUBSTRING(@enddate,9,2)as int)    

declare @day_count int
declare @index int=0
if @start_month<>@end_month --开始日期和结束日期不再同一个月份
begin    
        if @start_month = 2
        begin
            set @day_count = (28 + 1 - @start_day)
            if @start_year%4>0
            begin    
                set @day_count = (29 + 1 - @start_day)
            end            
            while @index<@day_count
            begin
                if (@start_day + @index)<10
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-02-0' + CAST((@start_day + @index) as varchar))
                end
                else
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-02-' + CAST((@start_day + @index) as varchar))
                end
                set @index = @index + 1
            end
            set @index = 0
        end
        else if @start_month = 1 or @start_month = 3 or @start_month = 5 or @start_month = 7 or @start_month = 8 or @start_month = 10 or @start_month = 12
        begin
            set @day_count = (31 + 1 - @start_day)
            while @index<@day_count
            begin
                if (@start_day + @index)<10
                begin
                    if @start_month<10
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))            
                    end
                    else
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))
                    end                    
                end
                else
                begin
                    if @start_month<10
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))            
                    end
                    else
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))
                    end    
                end
                set @index = @index + 1
            end
            set @index = 0
        end
        else if @start_month = 4 or @start_month = 6 or @start_month = 9 or @start_month = 11
        begin
            set @day_count = (30 + 1 - @start_day)
            while @index<@day_count
            begin
                if (@start_day + @index)<10
                begin
                    if @start_month<10
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))            
                    end
                    else
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))
                    end                    
                end
                else
                begin
                    if @start_month<10
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))            
                    end
                    else
                    begin
                        insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))
                    end    
                end
                set @index = @index + 1
            end
            set @index = 0
        end    
        
        set @day_count = @end_day
        while @index<@day_count
        begin
            if (@index + 1)<10
            begin
                if @end_month<10
                begin
                    insert into @date values(CAST(@end_year as varchar) + '-0' + CAST(@end_month as varchar) + '-0' + CAST((@index + 1) as varchar))
                end
                else
                begin
                    insert into @date values(CAST(@end_year as varchar) + '-' + CAST(@end_month as varchar) + '-0' + CAST((@index + 1) as varchar))
                end
            end
            else
            begin
                if @end_month<10
                begin
                    insert into @date values(CAST(@end_year as varchar) + '-0' + CAST(@end_month as varchar) + '-' + CAST((@index + 1) as varchar))
                end
                else
                begin
                    insert into @date values(CAST(@end_year as varchar) + '-' + CAST(@end_month as varchar) + '-' + CAST((@index + 1) as varchar))
                end            end
            set @index = @index + 1
        end
        set @index = 0
end
if @start_month = @end_month
begin
    set @day_count=@daycount
    while @index < @day_count
    begin
        if (@start_day + @index)<10
        begin
                if @start_month<10
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))
                end
                else
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-0' + CAST((@start_day + @index) as varchar))
                end
        end
        else
        begin
                if @start_month<10
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-0' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))
                end
                else
                begin
                    insert into @date values(CAST(@start_year as varchar) + '-' + CAST(@start_month as varchar) + '-' + CAST((@start_day + @index) as varchar))
                end
        end
        set @index = @index +1
    end
    set @index = 0        
end    
    RETURN
END

个人原创随笔，欢迎参考。。。。

转载于:https://www.cnblogs.com/rzm2wxm/p/7879680.html