SQL中DateTime.Utc.Ticks转日期字符串及更新记录报错问题

现有数据表数据如下：

Type     Stamp                     JobIdText   
104      638488466706836320        UL32443fdf
104      20240416074730            ILF20240416074730277A

需要将BigInt类型的Stamp（对应DateTime.Utc.Ticks）转换为20240416072013格式的日期字符串，并批量更新符合条件的记录，但执行更新SQL时出现错误：

error: Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query.

1. 正确转换Ticks为目标日期字符串 #

SQL Server中，.NET DateTime.Utc.Ticks是从0001-01-01开始的100纳秒计数，需先转换为SQL datetime类型，再格式化为指定字符串。提供两种实现方式：

方式一（兼容所有SQL Server版本）：

-- 转换为yyyyMMddHHmmss格式字符串
CONVERT(VARCHAR(14), DATEADD(NANOSECOND, (Stamp - 621355968000000000) * 100, '1970-01-01'), 112) + 
REPLACE(CONVERT(VARCHAR(8), DATEADD(NANOSECOND, (Stamp - 621355968000000000) * 100, '1970-01-01'), 108), ':', '')

注：621355968000000000是DateTime.UnixEpoch.Ticks，用于将.NET Ticks转换为Unix时间戳对应的纳秒数，再映射为SQL datetime。

方式二（SQL Server 2012及以上版本可用）：

-- 直接用FORMAT函数格式化
FORMAT(DATEADD(NANOSECOND, (Stamp - 621355968000000000) * 100, '1970-01-01'), 'yyyyMMddHHmmss')

2. 批量更新的正确SQL语句 #

错误核心是将datetime类型直接赋值给BigInt字段，SQL Server不允许这种隐式转换。需明确转换逻辑，并确保目标字段类型匹配：

场景1：将Stamp字段更新为日期字符串（需先将Stamp字段类型改为VARCHAR/CHAR）

UPDATE 你的表名
SET     Stamp = FORMAT(DATEADD(NANOSECOND, (Stamp - 621355968000000000) * 100, '1970-01-01'), 'yyyyMMddHHmmss')
WHERE   Type = 104 AND ISNUMERIC(Stamp) = 1 -- 仅处理BigInt格式的Stamp记录

场景2：更新其他字段（如JobIdText）

UPDATE 你的表名
SET     JobIdText = '自定义前缀' + FORMAT(DATEADD(NANOSECOND, (Stamp - 621355968000000000) * 100, '1970-01-01'), 'yyyyMMddHHmmss') + '自定义后缀'
WHERE   Type = 104 AND ISNUMERIC(Stamp) = 1

3. 错误原因说明 #

出现Implicit conversion from data type datetime to bigint is not allowed错误，是因为你在更新时未显式转换类型，直接将datetime结果赋值给了BigInt类型的字段，SQL Server禁止这种无明确转换的操作。

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