SQL SERVER获取月度列表

返回结果201210,201211,201212,201301,201302,201303,201304,201305,201306,201307,201308,201309,201310,201311,201312,201401,201402,201403,201404,201405,201406,201407,201408,201409,201410,201411,201412,201501,201502,201503,201504,201505,201506,201507,201508,201509,201510,201511,201512

-- 获取月度列表
if exists(select 1 from sysobjects where name = 'proc_GetDateMonthList' and type = 'p')
  drop proc proc_GetDateMonthList
GO
create proc proc_GetDateMonthList
 @BeginDate varchar(6)
,@EndDate varchar(6)
,@Delimiter varchar(1) = ','
as
/*
  
*/
declare
  @iBegin int, @iEnd int, @iBeginMon int, @iEndMon int, @iYear int, @iMon int, @iTempYear int, @iTempMon int, @iStart int , @iStop int,
  @sBeginMon varchar(2), @sEndMon varchar(2), @sResult varchar(8000), @s varchar(6)
begin
  if (LEN(@BeginDate) <> 6) or (LEN(@EndDate) <> 6)
  begin
    raiserror('日期格式错误！', 16, 1)
    return
  end
  if (CAST(@EndDate as int) - cast(@BeginDate as int)) < 0
  begin
    raiserror('日期范围错误！', 16, 1)
    return
  end
  if @BeginDate = @EndDate
  begin
    select @BeginDate
    return
  end
  select @iBegin = SUBSTRING(@BeginDate, 1, 4), @iEnd = SUBSTRING(@EndDate, 1, 4)
  set @iYear = @iEnd - @iBegin
  if @iYear < 0
  begin
    raiserror('日期范围错误！', 16, 1)
    return
  end
  if @iYear > 90
  begin
    raiserror('日期范围错误，最大跨年限度为90年！', 16, 1)
    return
  end
  -- 开始处理
  set @sResult = ''
  select @iBeginMon = SUBSTRING(@BeginDate, 5, 2), @iEndMon = SUBSTRING(@EndDate, 5, 2)
  -- 不跨年
  if @iYear = 0
  begin
    set @iMon = @iEndMon - @iBeginMon  
    if @iMon > 0
    begin
      set @sResult = @BeginDate
      set @iTempMon = 0
      while @iTempMon < @iMon - 1
      begin
        set @s = CAST(@BeginDate as int) + 1
        set @sResult = @sResult + @Delimiter + @s
        set @iTempMon = @iTempMon + 1
      end
      set @sResult = @sResult + @Delimiter + @EndDate
    end
  end
  -- 跨年
  if @iYear > 0
  begin
    -- 从开始到结束，每次+1，当月份 > 12 年度进1，月度归1
    select @iStart = @BeginDate, @iStop = @EndDate
    while @iStart <= @iStop
    begin
      select @iTempYear = SUBSTRING(convert(varchar(6),@iStart), 1, 4), @iTempMon = SUBSTRING(convert(varchar(6),@iStart), 5, 2)
      if @iTempMon > 12
      begin
        set @iStart = (@iTempYear + 1)*100 + 1
      end
      set @s = CONVERT(varchar(6), @iStart)
      --整合结果
      if LEN(@sResult) = 0
        set @sResult = @s
      else
        set @sResult = @sResult + @Delimiter + @s
      set @iStart = @iStart + 1
    end
  end
  select @sResult
end
GO
 
-- Test
exec proc_GetDateMonthList '201210', '201512'