sql server 自定義分割月功能詳解及實現代碼

2024-08-31 01:04:15

字體：大中小

來源：轉載

供稿：網友

在最近的項目開發過程中，遇到了Sql server自動分割月的功能需求，這里在網上整理下資料.

1、為何出現自定義分割月的需求

今天梳理一個平臺的所有函數時，發現了一個自定義分割月函數，也就是指定分割月的開始日索引值（可以從1-31閉區間內的任何一個值）來獲取指定日期所對應的分割月數值。這個函數當時是為了解決業務部門獲取非標準月（標準月就是從每個月的第一天到最后一天組成一個完成的標準月份）的統計匯總數據的。例如：如果指定分割月的開始日索引值為5則表示某個月的5號到下個月的4號之間作為一個完整的分割月；同樣地如果指定分割月的開始日索引值為1則表示標準月等等。

我仔細梳理了這個函數進行了重構簡化以及擴展，該自定義分割月函數的實現區別之前寫的SQL Server時間粒度系列----第3節旬、月時間粒度詳解文章中將一個整數值和月份日期相互轉換功能，這個是按照標準月來實現的，雖然思路大致相同，但是并沒有針對之前的月份日期和整數值轉換函數對來進行擴展而是獨立開發新的功能函數。也是為了盡量做到函數功能職責單一性、穩定性、可維護性以及可擴展性。

2、sql server實現自定義分割月功能

自定義分割月功能函數包括兩個標量函數：ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths獲取指定的日期在自定義分割月對應的分割月數值；ufn_SegMonth2Date獲取指定一個分割月數值賭對應的月份日期。

sql server 版本的實現T-SQL代碼如下：

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULLBEGIN  DROP FUNCTION [dbo].[ufn_SegMonths];ENDGO --==================================-- 功能：根據自定義月開始索引值獲取指定日期所在的自定義月數。-- 說明：自定義分割月數 = 年整數值*100 + 當前所在分割月值。-- 環境：SQL Server 2005+。-- 調用：SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。-- 創建：XXXX-XX-XX XX:XX-XX:XX XXX 創建函數實現。-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。--==================================CREATE FUNCTION [dbo].[ufn_SegMonths](   @dtmDate AS DATETIME            -- 日期  ,@tntSegStartIndexOfMonth AS INT = 15    -- 自定義分割月開始索引值（1-31))RETURNS INTASBEGIN    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;ENDGO IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULLBEGIN  DROP FUNCTION [dbo].[ufn_SegMonths2Date];ENDGO --==================================-- 功能：獲取自定義分割月數對應的自定義分割月日期。-- 說明：自定義分割月日期 = 自定義分割月數/100對應的年整數日期“組合”當前所在分割月值。-- 環境：SQL Server 2005+。-- 調用：SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。-- 創建：XXXX-XX-XX XX:XX-XX:XX XXX 創建函數實現。-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;--==================================CREATE FUNCTION [dbo].[ufn_SegMonths2Date](   @intSegMonths AS INT            -- 自定義分割月數)RETURNS DATETIMEASBEGIN      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));ENDGO

3、測試驗證效果

針對以上簡單的測試代碼如下：

DECLARE   @dtmStartDate AS DATETIME  ,@dtmEndDate AS DATETIME; SELECT   @dtmStartDate = '2000-01-01'  ,@dtmEndDate = '2016-12-31'; SELECT  [T1].*  ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDateFROM (  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 T1WHERE DATEPART(DAY, [T1].[CDate]) >= 27GO

效果截圖如下：

sql,server,自定義分割月,自定義分割月的實現,自定義分割月詳解