Azure SQL数据类型转换错误：nvarchar转bigint失败求助

错误信息 #

执行存储过程时触发如下错误：

Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to bigint.

问题根源 #

1. 字段类型不匹配：[x-dev].master表中的validUntil或updatedAt字段为nvarchar类型，当与bigint类型的变量做比较时，SQL Server会自动尝试将nvarchar字段值转换为bigint。若字段中存在非数字格式的内容，就会触发转换失败错误。
2. 浮点数计算的类型隐患：@MilisecondsYesterday的计算使用了浮点数1.3，运算结果为浮点数类型，赋值给bigint变量时会触发隐式转换，后续与DATEDIFF_BIG返回的bigint值运算时，会加剧类型转换的异常风险。

修复方案 #

1. 修正变量计算的类型问题 #

将@MilisecondsYesterday的计算结果显式转换为bigint，避免浮点数带来的类型问题：

SET @MilisecondsYesterday = CAST(1.3 * 24 * 60 * 60 * 1000 AS BIGINT)

如果追求更高精度，也可以直接用整数计算替代浮点数（1.3天=112320000毫秒）：

SET @MilisecondsYesterday = 112320000

2. 解决字段与变量的类型不匹配 #

有两种可选方案：

方案A：修改表字段类型（推荐）

如果业务允许，将validUntil和updatedAt字段改为bigint类型，从根源消除类型转换问题：

-- 先备份数据，再执行修改操作
ALTER TABLE [x-dev].master ALTER COLUMN validUntil BIGINT NULL;
ALTER TABLE [x-dev].master ALTER COLUMN updatedAt BIGINT NOT NULL; -- 根据实际NULL约束调整

方案B：查询时显式转换变量类型

若无法修改表结构，将bigint变量转换为nvarchar，与字段类型匹配，避免隐式转换：

SELECT id 
FROM [x-dev].master 
WHERE (validUntil < CAST(@deleteToDateYesterday AS NVARCHAR(20)) OR validUntil IS NULL) 
  AND updatedAt < CAST(@deleteToDate AS NVARCHAR(20));

注意：此方案可能导致字段索引失效，影响查询性能，仅作为临时解决手段。

修复后的完整代码 #

DECLARE @row UNIQUEIDENTIFIER
DECLARE @count BIGINT
DECLARE @deleteToDate BIGINT
DECLARE @deleteToDateYesterday BIGINT
DECLARE @DaysMiliseconds BIGINT
DECLARE @MilisecondsYesterday BIGINT
DECLARE @Time DATETIME

SET @Time = SYSDATETIME()
SET @count = 0

SET @DaysMiliseconds = CAST(365 AS BIGINT) * 24 * 60 * 60 * 1000
-- 显式转换为BIGINT，避免浮点数类型问题
SET @MilisecondsYesterday = CAST(1.3 * 24 * 60 * 60 * 1000 AS BIGINT)

-- 简化赋值写法，无需嵌套SELECT
SET @deleteToDate = DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSDATETIME()) - @DaysMiliseconds
SET @deleteToDateYesterday = DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSDATETIME()) - @MilisecondsYesterday

-- 字段为bigint时的查询写法
SELECT id 
FROM [x-dev].master 
WHERE (validUntil < @deleteToDateYesterday OR validUntil IS NULL) 
  AND updatedAt < @deleteToDate;

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