SQL Server 自訂分割月函數：非標準月份統計的彈性解決方案

業務常需依非標準月份（如每月 5 號至下月 4 號）進行統計。本文提供兩個標量函數 ufn_SegMonths 與 ufn_SegMonths2Date，支援自訂分割月起始日（1-31），實現日期與分割月代碼雙向轉換，並搭配數字輔助函數示範完整驗證流程。

一、需求背景

標準月為每月 1 號至月底。但許多業務（如薪資、帳單）以特定日期為分割點。例如：起始日設 28 號，則 1/28~2/27 為同一分割月。傳統日期函數無法直接支援，需自訂邏輯。

二、核心函數實作

1. ufn_SegMonths：日期 → 分割月代碼

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL DROP FUNCTION [dbo].[ufn_SegMonths];
GO
CREATE FUNCTION [dbo].[ufn_SegMonths] (
    @dtmDate AS DATETIME,
    @tntSegStartIndexOfMonth AS INT = 15  -- 分割起始日 1-31
)
RETURNS INT
AS
BEGIN
    -- 參數防呆
    IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)
        SET @tntSegStartIndexOfMonth = 15;

    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) SET @tntMonth = @tntMonth + 1;
    -- 跨年處理
    IF (@tntMonth > 12) BEGIN SET @intYears = @intYears + 1; SET @tntMonth = @tntMonth - 12; END

    RETURN @intYears * 100 + @tntMonth;  -- 格式：YYYYMM
END
GO

2. ufn_SegMonths2Date：分割月代碼 → 該分割月起始日期

IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL DROP FUNCTION [dbo].[ufn_SegMonths2Date];
GO
CREATE FUNCTION [dbo].[ufn_SegMonths2Date] (
    @intSegMonths AS INT  -- 格式 YYYYMM
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @dtmDefaultBasedate AS DATETIME = '1900-01-01';
    IF (@intSegMonths IS NULL OR @intSegMonths <= 0) RETURN @dtmDefaultBasedate;

    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

三、驗證測試

搭配數字輔助函數 ufn_GetNums 產生日期序列，測試起始日設 28 號的分割月邏輯：

DECLARE @dtmStartDate AS DATETIME = '2000-01-01',
        @dtmEndDate   AS DATETIME = '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

四、總結

• 將日期相關邏輯封裝為可重用函數，便於 BI 倉庫、報表直接呼叫。
• 函數職責單一、參數化設計，易於維護與擴展。
• 搭配數字輔助表/函數，可輕鬆產生大範圍測試資料進行邊界驗證。