SQL中如何将numeric(16)类型的yyyyMMddHHmmssff格式转为Datetime

问题根源 #

你的代码报错是因为时间部分的字符串截取逻辑错误：

• 原代码使用RIGHT('0000000000' + CAST(start_date AS varchar(16)), 10)截取了错误的字符段（取了最后10位，包含部分日期内容），导致拼接后的字符串不符合合法日期时间格式，无法完成转换。
• 直接将numeric类型转varchar会丢失前导零（如果数值不足16位），进一步导致日期/时间段截取错误。

正确解决方案 #

先确保将numeric(16)转换为固定16位的字符串（补前导零），再拆分日期和时间段分别格式化，最后拼接转换为Datetime：

SELECT 
    -- 转换为Datetime类型
    CONVERT(DATETIME, 
        -- 格式化日期部分：yyyy-MM-dd
        STUFF(STUFF(SUBSTRING(str_start_date, 1, 8), 5, 0, '-'), 8, 0, '-') + ' ' +
        -- 格式化时间部分：HH:mm:ss.ff
        STUFF(STUFF(STUFF(SUBSTRING(str_start_date, 9, 8), 3, 0, ':'), 6, 0, ':'), 9, 0, '.')
    ) AS converted_datetime,
    -- 直接输出指定格式的字符串（带T）
    FORMAT(
        CONVERT(DATETIME, 
            STUFF(STUFF(SUBSTRING(str_start_date, 1, 8), 5, 0, '-'), 8, 0, '-') + ' ' +
            STUFF(STUFF(STUFF(SUBSTRING(str_start_date, 9, 8), 3, 0, ':'), 6, 0, ':'), 9, 0, '.')
        ), 'yyyy-MM-ddTHH:mm:ss.ff'
    ) AS formatted_datetime_str
FROM (
    -- 补前导零，确保字符串为固定16位
    SELECT RIGHT('0000000000000000' + CAST(start_date AS VARCHAR(16)), 16) AS str_start_date
    FROM your_table -- 替换为你的实际表名
) t;

代码说明 #

1. 补前导零：通过RIGHT('0000000000000000' + CAST(start_date AS VARCHAR(16)), 16)确保字符串始终是16位，避免前导零丢失导致的位数错误。
2. 日期格式化：对前8位（yyyyMMdd）用STUFF插入-，转为标准日期格式yyyy-MM-dd。
3. 时间格式化：对后8位（HHmmssff）用三次STUFF分别插入:和.，转为标准时间格式HH:mm:ss.ff。
4. 转换与输出：先拼接日期时间字符串转成Datetime类型；若需要直接输出2023-03-20T12:12:12.01格式，使用FORMAT函数指定格式即可。

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