SQL Server与Oracle的months_between函数输出差异及对齐需求

我正在一个需要同时在SQL Server（使用Synapse）和Oracle中计算两个日期之间月份数的项目中工作。已在SQL Server中创建了一个用户定义函数（UDF）来复刻Oracle的MONTHS_BETWEEN函数，但输出存在细微差异，该差异会随时间累积，进而导致指标计算出现巨大偏差。

现有实现代码 #

CREATE FUNCTION [TFM].[UDF_MONTHS_BETWEEN] (
    @start_date_p [DATE]
    ,@end_date_p [DATE]
    )
RETURNS FLOAT
AS
BEGIN
    DECLARE @count_v FLOAT;
    DECLARE @daysinmonth_v FLOAT;
    DECLARE @dayofmonth_v FLOAT;

    -- Count whole months 
    SET @count_v = DATEDIFF(MONTH, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@start_date_p), MONTH(@start_date_p), 1)), DATEFROMPARTS(YEAR(@end_date_p), MONTH(@end_date_p), 1));
    -- Days in start month 
    SET @daysinmonth_v = DAY(EOMONTH(@start_date_p));
    -- Get fractional start month 
    SET @dayofmonth_v = DAY(@start_date_p);
    SET @count_v = @count_v + ((@daysinmonth_v - (@dayofmonth_v - 1)) * (1.0 / @daysinmonth_v));
    -- Days in end month 
    SET @daysinmonth_v = DAY(EOMONTH(@end_date_p));
    -- Get fractional end month 
    SET @dayofmonth_v = DAY(@end_date_p);
    SET @count_v = @count_v + ((@dayofmonth_v - 1) * (1.0 / @daysinmonth_v));

    RETURN ABS(@count_v);
END

测试用例 #

测试代码 #

SQL Server #

SELECT TFM.UDF_MONTHS_BETWEEN('4/28/2024','7/27/2024') 

Oracle #

SELECT ABS(MONTHS_BETWEEN('28-APR-24','27-JUL-24')) FROM DUAL;

问题分析 #

现有UDF的计算逻辑与Oracle官方MONTHS_BETWEEN的逻辑完全不符：

• Oracle的MONTHS_BETWEEN对于非同月同日/非月末的日期，小数部分基于31天固定月份计算，而非按起止月份的实际天数拆分。
• 官方核心规则：
  1. 若两个日期是同月同日，或都是当月最后一天，结果为整数；
  2. 其他情况按公式计算：(结束年-开始年)*12 + (结束月-开始月) + (结束日-开始日)/31

修正后的UDF代码 #

CREATE FUNCTION [TFM].[UDF_MONTHS_BETWEEN] (
    @start_date_p DATE,
    @end_date_p DATE
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @result FLOAT;
    DECLARE @start_day INT = DAY(@start_date_p);
    DECLARE @end_day INT = DAY(@end_date_p);
    DECLARE @is_start_eom BIT = CASE WHEN @start_date_p = EOMONTH(@start_date_p) THEN 1 ELSE 0 END;
    DECLARE @is_end_eom BIT = CASE WHEN @end_date_p = EOMONTH(@end_date_p) THEN 1 ELSE 0 END;

    -- 处理特殊场景：同月同日，或均为月末
    IF (@start_day = @end_day) OR (@is_start_eom = 1 AND @is_end_eom = 1)
    BEGIN
        SET @result = DATEDIFF(MONTH, @start_date_p, @end_date_p);
    END
    ELSE
    BEGIN
        -- 严格遵循Oracle官方公式计算
        SET @result = (YEAR(@end_date_p) - YEAR(@start_date_p)) * 12.0
                    + (MONTH(@end_date_p) - MONTH(@start_date_p))
                    + (@end_day - @start_day) / 31.0;
    END

    -- 返回绝对值，匹配Oracle ABS(MONTHS_BETWEEN(...))的行为
    RETURN ABS(@result);
END

验证结果 #

用修正后的UDF执行测试用例：

• 测试1输出为30/31≈0.967741935483871，与Oracle完全一致；
• 测试2输出为92/31≈2.967741935483871，与Oracle完全一致。

内容的提问来源于stack exchange，提问作者KKU