SQL Server 2016中DATEPART(MS,datetime)返回值错误问题

问题确认 #

这是SQL Server 2016兼容性级别（130）下的已知问题。当数据库兼容性级别升级到130后，DATEPART函数提取DATETIME类型的毫秒值时，末尾为"7"的毫秒值会出现精度丢失（如示例中.927返回926），而在SQL Server 2008实例或兼容性级别为2014（120）的数据库中无此问题。

问题原因 #

DATETIME类型的底层存储精度约为3.33毫秒，旧兼容性级别下SQL Server对这类值做了近似显示处理，使得输入的.927被识别为对应近似值；而升级到130级别后，处理逻辑更贴近实际存储的二进制值，导致DATEPART返回的是存储的近似值而非输入的显示值。

解决方案 #

1. 临时修复（单处修改） #

将DATETIME值转换为DATETIME2(3)后再调用DATEPART，DATETIME2支持精确到毫秒的存储，转换后可正确保留原始输入的毫秒值：

DECLARE @datetime_value DATETIME
SET @datetime_value = '2000-01-01 00:00:00.927'
SELECT DATEPART(MS, CONVERT(DATETIME2(3), @datetime_value))

执行后返回结果为927，符合预期。

2. 批量优化方案 #

若需修改大量UDF和存储过程，可创建通用包装函数封装转换逻辑，减少修改范围：

CREATE FUNCTION dbo.GetDateTimeMillisecond(@dt DATETIME)
RETURNS INT
AS
BEGIN
    RETURN DATEPART(MS, CONVERT(DATETIME2(3), @dt))
END

后续将所有DATEPART(MS, <DATETIME变量>)的调用替换为dbo.GetDateTimeMillisecond(<DATETIME变量>)即可。

3. 长期根治方案 #

逐步将数据库中所有DATETIME类型字段迁移为DATETIME2(3)，从根源上避免精度问题，同时利用DATETIME2的更高精度特性提升数据准确性。

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