if exists(select 1 from sysobjects where type='TF' and name='ATPublic_TF_GetSummaryDate')    drop function ATPublic_TF_GetSummaryDategocreate function ATPublic_TF_GetSummaryDate(    @calltype varchar(1)='',    --0:年 1:月    1:日    @startdate DATE,            --开始日期    @enddate DATE                --结束日期)  /***     Author:     Create date: 2018-01-18    Description: 循环返回日期       Example: ***/RETURNS @retDateValue TABLE (datevalue varchar(32))                                                                               as    begin    if @calltype='0'    begin        insert into @retDateValue(datevalue)        select substring(convert(varchar(32),DATEADD(YEAR,number,@startdate),21),1,4) as yearvalue                                                                                      from master..spt_values WHERE TYPE = 'P' AND DATEADD(YEAR,number,@startdate) <= @enddate                    end    else if @calltype='1'    begin        insert into @retDateValue(datevalue)        select substring(convert(varchar(32),DATEADD(MONTH,number,@startdate),21),1,7) as yearvalue                                                                                      from master..spt_values WHERE TYPE = 'P' AND DATEADD(MONTH,number,@startdate) < @enddate                    end    else if @calltype='2'    begin        insert into @retDateValue(datevalue)        select convert(varchar(32),DATEADD(DAY,number,@startdate),21)+' 00:00:00' as yearvalue                                                                                      from master..spt_values WHERE TYPE = 'P' AND DATEADD(DAY,number,@startdate) < @enddate                    end    returnend                                                                        go

调用示例：

declare @beginDate varchar(32)='', @endDate varchar(32)='',@datebefore datetimeselect @beginDate='2017-01-01',@endDate='2017-05-31'select @datebefore=CONVERT(datetime,@beginDate)select datevalue   from ATPublic_TF_GetSummaryDate('0',@beginDate,@endDate)select datevalue from ATPublic_TF_GetSummaryDate('1',@beginDate,dateadd(YEAR,1,@datebefore))select datevalue from ATPublic_TF_GetSummaryDate('1',@beginDate,dateadd(MONTH,1,@datebefore))select datevalue from ATPublic_TF_GetSummaryDate('2',@beginDate,dateadd(MONTH,1,@datebefore))