sql server 自定義分割月功能詳解及實(shí)現(xiàn)代碼

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths];
END
GO
 
--==================================
-- 功能：根據(jù)自定義月開(kāi)始索引值獲取指定日期所在的自定義月數(shù)。
-- 說(shuō)明：自定義分割月數(shù) = 年整數(shù)值*100 + 當(dāng)前所在分割月值。
-- 環(huán)境：SQL Server 2005+。
-- 調(diào)用：SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。
-- 創(chuàng)建：XXXX-XX-XX XX:XX-XX:XX XXX 創(chuàng)建函數(shù)實(shí)現(xiàn)。
-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths]
(
   @dtmDate AS DATETIME            -- 日期
  ,@tntSegStartIndexOfMonth AS INT = 15    -- 自定義分割月開(kāi)始索引值（1-31)
)
RETURNS INT
AS
BEGIN  
  IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)
  BEGIN
    SET @tntSegStartIndexOfMonth = 15;
  END
 
  DECLARE
     @intYears AS INT
    ,@tntMonth AS TINYINT
    ,@sntDay AS SMALLINT;    
  SELECT
     @intYears = DATEDIFF(YEAR, '1900-01-01', @dtmDate)
    ,@tntMonth = DATEPART(MONTH, @dtmDate)
    ,@sntDay = DATEPART(DAY, @dtmDate);
 
  IF (@sntDay >= @tntSegStartIndexOfMonth)
  BEGIN
    SET @tntMonth = @tntMonth + 1;  
  END
 
  IF (@tntMonth > 12)
  BEGIN
    SELECT
       @intYears = @intYears + 1
      ,@tntMonth = @tntMonth - 12;
  END
 
  RETURN @intYears * 100 + @tntMonth;
END
GO
 
IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths2Date];
END
GO
 
--==================================
-- 功能：獲取自定義分割月數(shù)對(duì)應(yīng)的自定義分割月日期。
-- 說(shuō)明：自定義分割月日期 = 自定義分割月數(shù)/100對(duì)應(yīng)的年整數(shù)日期“組合”當(dāng)前所在分割月值。
-- 環(huán)境：SQL Server 2005+。
-- 調(diào)用：SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。
-- 創(chuàng)建：XXXX-XX-XX XX:XX-XX:XX XXX 創(chuàng)建函數(shù)實(shí)現(xiàn)。
-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths2Date]
(
   @intSegMonths AS INT            -- 自定義分割月數(shù)
)
RETURNS DATETIME
AS
BEGIN    
  DECLARE @dtmDefaultBasedate AS DATETIME;
  SET @dtmDefaultBasedate = '1900-01-01';
 
  IF ((@intSegMonths IS NULL) OR (@intSegMonths = 0))
  BEGIN
    RETURN @dtmDefaultBasedate;
  END
 
  DECLARE
     @intYears AS INT
    ,@intMonth AS INT;  
  SELECT
     @intYears = @intSegMonths / 100
    ,@intMonth = @intSegMonths % 100;  
 
  RETURN DATEADD(MONTH, @intMonth - 1, DATEADD(YEAR, @intYears, @dtmDefaultBasedate));
END
GO
 

DECLARE
   @dtmStartDate AS DATETIME
  ,@dtmEndDate AS DATETIME;
 
SELECT
   @dtmStartDate = '2000-01-01'
  ,@dtmEndDate = '2016-12-31';
 
SELECT
  [T1].*
  ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDate
FROM (
  SELECT
    [T].[CDate]
    ,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths
 
  FROM (
    SELECT
      DATEADD(DAY, [Num], @dtmStartDate) AS CDate
    FROM
      [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))
  ) AS T
  WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31'
) AS T1
WHERE DATEPART(DAY, [T1].[CDate]) >= 27
GO