获取月度列表 

 1 -- 获取月度列表
 2 if exists(select 1 from sysobjects where name = 'proc_GetDateMonthList' and type = 'p')
 3   drop proc proc_GetDateMonthList
 4 GO
 5 create proc proc_GetDateMonthList
 6  @BeginDate varchar(6)
 7 ,@EndDate varchar(6)
 8 ,@Delimiter varchar(1) = ','
 9 as
10 /*
11   Create By: www.liuju.net 
12   QQ群：327307058
13   Date: 2013-10-09 
14 */
15 declare
16   @iBegin int, @iEnd int, @iBeginMon int, @iEndMon int, @iYear int, @iMon int, @iTempYear int, @iTempMon int, @iStart int , @iStop int,
17   @sBeginMon varchar(2), @sEndMon varchar(2), @sResult varchar(8000), @s varchar(6)
18 begin
19   if (LEN(@BeginDate) <> 6) or (LEN(@EndDate) <> 6)
20   begin
21     raiserror('日期格式错误！', 16, 1)
22     return
23   end
24   if (CAST(@EndDate as int) - cast(@BeginDate as int)) < 0
25   begin
26     raiserror('日期范围错误！', 16, 1)
27     return
28   end
29   if @BeginDate = @EndDate
30   begin
31     select @BeginDate
32     return
33   end
34   select @iBegin = SUBSTRING(@BeginDate, 1, 4), @iEnd = SUBSTRING(@EndDate, 1, 4)
35   set @iYear = @iEnd - @iBegin
36   if @iYear < 0
37   begin
38     raiserror('日期范围错误！', 16, 1)
39     return
40   end
41   if @iYear > 90
42   begin
43     raiserror('日期范围错误，最大跨年限度为90年！', 16, 1)
44     return
45   end
46   -- 开始处理
47   set @sResult = ''
48   select @iBeginMon = SUBSTRING(@BeginDate, 5, 2), @iEndMon = SUBSTRING(@EndDate, 5, 2)
49   -- 不跨年
50   if @iYear = 0
51   begin
52     set @iMon = @iEndMon - @iBeginMon   
53     if @iMon > 0
54     begin
55       set @sResult = @BeginDate
56       set @iTempMon = 0
57       while @iTempMon < @iMon - 1
58       begin
59         set @s = CAST(@BeginDate as int) + 1
60         set @sResult = @sResult + @Delimiter + @s
61         set @iTempMon = @iTempMon + 1 
62       end
63       set @sResult = @sResult + @Delimiter + @EndDate
64     end 
65   end
66   -- 跨年
67   if @iYear > 0
68   begin
69     -- 从开始到结束，每次+1，当月份 > 12 年度进1，月度归1
70     select @iStart = @BeginDate, @iStop = @EndDate
71     while @iStart <= @iStop
72     begin
73       select @iTempYear = SUBSTRING(convert(varchar(6),@iStart), 1, 4), @iTempMon = SUBSTRING(convert(varchar(6),@iStart), 5, 2)
74       if @iTempMon > 12
75       begin
76         set @iStart = (@iTempYear + 1)*100 + 1
77       end
78       set @s = CONVERT(varchar(6), @iStart)
79       --整合结果
80       if LEN(@sResult) = 0
81         set @sResult = @s
82       else
83         set @sResult = @sResult + @Delimiter + @s
84       set @iStart = @iStart + 1
85     end
86   end
87   select @sResult
88 end
89 GO
90 
91 -- Test
92 exec proc_GetDateMonthList '201210', '201512'

 

posted @ 2013-12-23 23:15  [望梅止渴]  阅读(45)  评论(0)    收藏  举报