SQL Server获取月度列表

-- 获取月度列表
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'