SQL Server中将numeric(17,9)格式数值转换为带时间的DateTime方法

算力阿峰

2026-9-24

解决SQL Server中numeric(17,9)格式时间转DateTime丢失时间部分的方案

你之前转换丢失时间的核心原因是仅处理了numeric值的整数部分（对应yyyyMMdd格式的日期），未解析小数部分对应的时分秒毫秒信息，以下是两种可行方案：

方案1：字符串拼接转换（逻辑直观，适合小批量数据或校验场景）

通过拆分整数和小数部分，分别格式化为标准日期、时间字符串后拼接转换，代码示例：

DECLARE @num_datetime NUMERIC(17,9) = 20210928.110424000;

SELECT 
    CONVERT(DATETIME2(3),
        -- 处理日期部分：整数部分转成yyyy-MM-dd格式
        STUFF(STUFF(CAST(CAST(@num_datetime AS INT) AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-')
        + ' '
        -- 处理时间部分：小数部分转成HH:mm:ss.fff格式
        + STUFF(STUFF(STUFF(
            RIGHT('000000000' + CAST(CAST((@num_datetime - CAST(@num_datetime AS INT)) * 1000000000 AS BIGINT) AS VARCHAR(9)), 9)
            , 3, 0, ':'), 6, 0, ':'), 9, 0, '.')
    ) AS converted_datetime

执行结果为2021-09-28 11:04:24.000，完全保留原始时间精度。

方案2：日期加时间偏移（性能更优，适合数仓大批量数据处理）

先转换整数部分为基准日期，再将小数部分换算为时间偏移量叠加到基准日期上，代码示例：

DECLARE @num_datetime NUMERIC(17,9) = 20210928.110424000;
DECLARE @time_part BIGINT = CAST((@num_datetime - CAST(@num_datetime AS INT)) * 1000000000 AS BIGINT);

SELECT 
    CONVERT(DATETIME2(3), CAST(CAST(@num_datetime AS INT) AS VARCHAR(8)), 112)
    + DATEADD(MILLISECOND,
        (@time_part / 1000000) * 3600000 -- 小时转毫秒
        + (@time_part % 1000000 / 10000) * 60000 -- 分钟转毫秒
        + (@time_part % 10000 / 10) * 1000 -- 秒转毫秒
        + (@time_part % 10), -- 毫秒
        0
    ) AS converted_datetime