SQL Server 自定义分割月函数实现：跨越自然月的业务统计

木白

发布时间：2026-10-13 | 2人浏览过

原创

在处理BI数据统计或财务周期报表时，常需避开1-30/31日的自然月界限，采用自定义起始日（如每月28日后归入下月）进行聚合。本文将提供一套基于 SQL Server 的自定义分割月解决方案，包含将日期映射为分割月序号、以及由序号反推基准日期的完整 T-SQL 函数实现。

背景与目标

标准日历月通常从每月1日开始至月末结束，但在零售、物流或财务结算等场景中，“账期”往往以月中某日为界。例如，若设定分割月开始索引为 15，则当月的 15 日至次月的 14 日构成一个独立的“分割月”。这种机制能有效规避月末最后一天归属不确定带来的统计偏差。

准备条件

该方案专为 SQL Server 2005 及以上版本设计，依赖 T-SQL 标量函数（Scalar Function）。核心逻辑利用整数除法与取模运算，将年月信息压缩编码为单一整数，从而简化分区键生成与范围查询。

实现步骤

第1步：创建日期转分割月数值函数

目的：接收任意日期与分割起始日，返回唯一的 YYYYMM 格式整型标识。该数值可直接作为数据仓库中的日期维度关联键。

入口：新建标量函数 [dbo].[ufn_SegMonths]。

动作与参数：

@dtmDate：输入待转换的 DATETIME 类型日期。
@tntSegStartIndexOfMonth：指定每月几号为新分割月的开始，默认值 15。有效范围需校验，超出范围时自动修正为默认值。
逻辑说明：先计算距基准年（1900年）的年份差，再获取当前月份与日。若当前日期 >= 起始日，则月份累加；若累加后月份 > 12，则年份加1并重置月份。

T-SQL 代码：

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths];
END
GO
 
--==================================
-- 功能：根据自定义月开始索引值获取指定日期所在的自定义月数。
-- 说明：自定义分割月数 = 年整数值*100 + 当前所在分割月值。
-- 环境：SQL Server 2005+。
-- 调用：SET @intSegMonths = dbo.ufn_SegMonths('2008-01-14', 15)。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths]
(
   @dtmDate AS DATETIME            -- 日期
  ,@tntSegStartIndexOfMonth AS INT = 15    -- 自定义分割月开始索引值（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

第2步：创建分割月数值转日期函数

目的：逆向操作，接收上述生成的整型 SegMonths 值，还原为对应分割周期的第一个基准日期。常用于前端展示周期名称或初始化报表时间轴。

入口：新建标量函数 [dbo].[ufn_SegMonths2Date]。

动作与参数：

@intSegMonths：输入 YYYYMM 格式的整型分割月值（如 202306）。
逻辑说明：通过 / 100 获取年份偏移量，通过 % 100 获取月份。基于 1900-01-01 添加相应的年和月即可得到基准日。

T-SQL 代码：

IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths2Date];
END
GO
 
--==================================
-- 功能：获取自定义分割月数对应的自定义分割月日期。
-- 说明：自定义分割月日期 = 自定义分割月数/100对应的年整数日期“组合”当前所在分割月值。
-- 环境：SQL Server 2005+。
-- 调用：SET @dtmSegMonthDate = dbo.ufn_SegMonths2Date(202306)。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths2Date]
(
   @intSegMonths AS INT            -- 自定义分割月数
)
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

第3步：测试验证跨月效果

目的：验证函数在跨年、月末边界条件下的准确性。本示例设置起始日为当月第 28 天，观察跨月数据如何归类。

动作：使用辅助表生成连续日期序列，应用 ufn_SegMonths 计算分割月编号，并通过 ufn_SegMonths2Date 回显对应日期。

关键结果图片：

测试代码：

注：以下代码引用了内置或用户定义的数字表函数 [dbo].[ufn_GetNums] 用于快速生成日期序列。

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