Sqlsever 时间拆分存储过程

最新推荐文章于 2023-09-18 13:46:20 发布

.NET跨平台

最新推荐文章于 2023-09-18 13:46:20 发布

阅读量280

点赞数 1

分类专栏： SqlServer数据库

本文链接：https://blog.csdn.net/WuLex/article/details/110099193

版权

SqlServer数据库专栏收录该内容

180 篇文章 33 订阅

订阅专栏

创建自定义的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

在这里插入图片描述

`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