Sqlsever 时间拆分存储过程

不念不忘少年蓝@ 2021-06-26 16:06 480阅读 0赞

## 创建自定义的Split（表值函数） ##

CREATE FUNCTION [dbo].[Split](@String    VARCHAR(8000), 
                                  @Delimiter CHAR(1)) 
    returns @temptable TABLE ( 
      items VARCHAR(8000)) 
    AS 
      BEGIN 
          DECLARE @idx INT 
          DECLARE @slice VARCHAR(8000) 
    
          SELECT @idx = 1 
    
          IF Len(@String) < 1 
              OR @String IS NULL 
            RETURN 
    
          WHILE @idx != 0 
            BEGIN 
                SET @idx =Charindex(@Delimiter, @String) 
    
                IF @idx != 0 
                  SET @slice =LEFT(@String, @idx - 1) + '-01' 
                ELSE 
                  SET @slice = @String + '-01' 
    
                IF( Len(@slice) > 0 ) 
                  INSERT INTO @temptable 
                              (items) 
                  VALUES     (@slice) 
    
                SET @String =RIGHT(@String, Len(@String) - @idx) 
    
                IF Len(@String) = 0 
                  BREAK 
            END 
    
          RETURN 
      END

## 直接执行示例 ##

BEGIN 
        SELECT Cast(items AS DATETIME)               AS rangetime, 
               Row_number() 
                 OVER( 
                   ORDER BY items)                   AS idnum, 
               Datepart(yy, Cast(items AS DATETIME)) AS timeyear, 
               Dateadd(year, Datediff(year, 0, Dateadd(year, 1, 
                                               Cast(items AS DATETIME)) 
                             ), -1) 
                                                     AS lastday 
        INTO   #resulttb 
        FROM   dbo.Split('2018-05~2019-05', '~') AS tdata 
    
      DECLARE @onetime VARCHAR(50); 
          DECLARE @twotime VARCHAR(50); 
          DECLARE @wheresql VARCHAR(200);  
          DECLARE @rangetime1 VARCHAR(500); 
          DECLARE @rangetime2 VARCHAR(500); 
          DECLARE @lastday1 VARCHAR(500); 
          DECLARE @lastday2 VARCHAR(500); 
    
          SELECT @onetime = aaa.timeyear, 
                 @twotime = bbb.timeyear, 
                 @rangetime1 = CONVERT(VARCHAR, aaa.rangetime, 23), 
                 @rangetime2 = CONVERT(VARCHAR, bbb.rangetime, 23), 
                 @lastday1 = CONVERT(VARCHAR, aaa.lastday, 23)+' 23:59:59', 
                 @lastday2 = CONVERT(VARCHAR, bbb.lastday, 23)+' 23:59:59'
          FROM   #resulttb AS aaa 
                 JOIN #resulttb bbb 
                 ON aaa.idnum + 1 = bbb.idnum 
    
          --PRINT( @onetime ) 
          --PRINT( @twotime ) 
          IF( @onetime = @twotime ) 
            SET @wheresql= 'paymsg between ''' + @rangetime1 + ''' AND ''' + @rangetime2 +' 23:59:59' + ''''; 
          ELSE 
            SET @wheresql= 'paymsg between ''' + @rangetime1 + ''' AND ''' + @lastday1 + '''' + ' OR ' 
                           + 'paymsg between ''' + @rangetime2 + ''' AND ''' + @lastday2 + '''' 
    	  SELECT * FROM  #resulttb 
          DROP TABLE #resulttb 
    END

![在这里插入图片描述][20201125095632388.png]

## `Getrangewheresql` 存储过程 ##

CREATE PROCEDURE Getrangewheresql (@String    VARCHAR(8000), 
                                       @Delimiter CHAR(1), 
                                       @wheresql  VARCHAR(200) output) 
    AS 
      BEGIN 
          SET nocount ON; 
    
          SELECT Cast(items AS DATETIME)               AS rangetime, 
                 Row_number() 
                   OVER( 
                     ORDER BY items)                   AS idnum, 
                 Datepart(yy, Cast(items AS DATETIME)) AS timeyear, 
                 Dateadd(year, Datediff(year, 0, Dateadd(year, 1, 
                                                 Cast(items AS DATETIME)) 
                               ), -1) 
                                                       AS lastday 
          INTO   #resulttb 
          FROM   dbo.Split(@String, @Delimiter) AS tdata 
    
          DECLARE @onetime VARCHAR(50); 
          DECLARE @twotime VARCHAR(50); 
          --DECLARE @wheresql VARCHAR(200); 
          DECLARE @rangetime1 VARCHAR(500); 
          DECLARE @rangetime2 VARCHAR(500); 
          DECLARE @lastday1 VARCHAR(500); 
          DECLARE @lastday2 VARCHAR(500); 
    
          SELECT @onetime = aaa.timeyear, 
                 @twotime = bbb.timeyear, 
                 @rangetime1 = CONVERT(VARCHAR, aaa.rangetime, 23), 
                 @rangetime2 = CONVERT(VARCHAR, bbb.rangetime, 23), 
                 @lastday1 = CONVERT(VARCHAR, aaa.lastday, 23)+' 23:59:59', 
                 @lastday2 = CONVERT(VARCHAR, bbb.lastday, 23)+' 23:59:59'
          FROM   #resulttb AS aaa 
                 JOIN #resulttb bbb 
                   ON aaa.idnum + 1 = bbb.idnum 
    
          --PRINT( @onetime ) 
          --PRINT( @twotime ) 
          IF( @onetime = @twotime ) 
            SET @wheresql= 'paymsg between ''' + @rangetime1 + ''' AND ''' + @rangetime2 +' 23:59:59' + ''''; 
          ELSE 
            SET @wheresql= 'paymsg between ''' + @rangetime1 + ''' AND ''' + @lastday1 + '''' + ' OR ' 
                           + 'paymsg between ''' + @rangetime2 + ''' AND ''' + @lastday2 + '''' 
    
          DROP TABLE #resulttb 
      END

## 执行存储过程 ##

DECLARE @sqlwhere VARCHAR(200)
    
    EXEC Getrangewheresql '2018-05~2018-11','~' ,@sqlwhere output
    
    print @sqlwhere
    
    EXEC Getrangewheresql '2018-05~2019-10','~' ,@sqlwhere output
    
    print @sqlwhere

![在这里插入图片描述][20201125103159525.png]

[20201125095632388.png]: /images/20210626/a15ae60214dd4fd88d58163711fc0437.png
[20201125103159525.png]: /images/20210626/1d91c103fb104261952e0e629848d70f.png