SQL Server获取月度列表-白红宇

SQL Server获取月度列表

阅读量：5102 次

发布时间：2019-06-13

本文共 2231 字，大约阅读时间需要 7 分钟。

-- 获取月度列表if exists(select 1 from sysobjects where name = 'proc_GetDateMonthList' and type = 'p')  drop proc proc_GetDateMonthListGOcreate 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 @sResultendGO-- Testexec proc_GetDateMonthList '201210', '201512'

转载于:https://www.cnblogs.com/junko/p/3726225.html

你可能感兴趣的文章

主要的几个脑网络——整理自eegfmri的博客

查看>>

leetcode 459. 重复的子字符串(Repeated Substring Pattern)

查看>>

CABasicAnimation animationWithKeyPath Types