SQL Server 将格式化存储的bigint值转换为datetime遇类型报错求助

问题根因拆解 #

• 当前存储的bigint值不属于Unix时间戳体系：示例值2021021209295600000的固定结构为「4位年+2位月+2位日+2位时+2位分+2位秒+5位补0」，和通用方案中基于秒/毫秒偏移量的Unix时间戳逻辑完全不匹配，直接套用转换公式必然得到错误结果。
• 原有自定义代码报错原因：dateadd计算的偏移值量级远超出datetime类型的支持范围，触发隐式类型转换，最终将部分计算结果转为time类型，与datetime类型做加法时出现类型不兼容报错，本质是时间分量拆分逻辑完全错误。

可落地的转换实现 #

以下两种方案均可直接输出目标值2021-02-12 09:29:56，适配当前存储规则：

方案1：数值结构化拆分（无字符串转换开销） #

declare @starttime as bigint;
set @starttime = 2021021209295600000;

-- 按存储固定结构拆分各时间分量
declare @year int = @starttime / 1000000000000000;
declare @month int = (@starttime / 10000000000000) % 100;
declare @day int = (@starttime / 100000000000) % 100;
declare @hour int = (@starttime / 1000000000) % 100;
declare @minute int = (@starttime / 10000000) % 100;
declare @second int = (@starttime / 100000) % 100;

-- 拼接生成目标datetime
select datefromparts(@year, @month, @day) + timefromparts(@hour, @minute, @second, 0, 0) as target_datetime

方案2：字符串截取转换（逻辑直观易维护） #

declare @starttime as bigint;
set @starttime = 2021021209295600000;

select convert(datetime, 
    concat(
      left(cast(@starttime as varchar(20)), 8),
      ' ',
      substring(cast(@starttime as varchar(20)), 9, 2),
      ':',
      substring(cast(@starttime as varchar(20)), 11, 2),
      ':',
      substring(cast(@starttime as varchar(20)), 13, 2)
    )
) as target_datetime

常见踩坑提醒 #

• 禁用通用Unix时间戳转换公式：示例值的量级为10^16，按秒级Unix时间戳计算将得到公元5万年后的日期，按毫秒级计算将得到公元26万年后的日期，完全偏离目标结果。
• 若坚持使用dateadd做偏移计算，需严格匹配时间粒度，避免将秒级偏移值按小时/天粒度叠加，从根源上避免类型不兼容报错。

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