SSMS中如何在CONVERT(nvarchar,ISNULL)查询内嵌入CAST解决日期转换错误

报错原因 #

你遇到的日期转换报错是两个问题共同导致的：

• SQL的执行顺序里WHERE子句的执行优先级高于SELECT子句，所以你在WHERE里直接引用SELECT中定义的别名Call Date不会生效，你写的'Call Date' >= @LOCLastMod实际是在尝试把字符串常量'Call Date'转为datetime类型和参数比较，直接触发转换失败。
• 即便别名可以正常引用，你把日期字段转成了nvarchar类型再和datetime类型的参数做比较，也会触发隐式类型转换，要么报错要么会导致索引失效、查询性能下降。

修复方案 #

推荐优先修改WHERE条件，直接用原始日期字段做比较，性能最优，不需要改动你SELECT中Call Date的原有定义：
把WHERE子句中的

AND 'Call Date' >= @LOCLastMod

替换为

AND ISNULL(T.ActivityDate, T.CreatedDate) >= @LocLastMod

如果你确实需要按要求在指定的SELECT代码行添加CAST函数（比如有其他业务逻辑要求），修改后的代码行如下：

CAST(CONVERT(nvarchar(25), ISNULL(T.ActivityDate, T.CreatedDate), 101) AS DATETIME) AS 'Call Date'

注意：即便你修改了SELECT里的Call Date定义，还是不能直接在WHERE中引用这个别名，要么用子查询/CTE把结果集包裹后再做过滤，优先推荐上面修改WHERE条件的方案。

修改后完整代码 #

--USE LOCAL VARIABLES FOR SPEED
DECLARE @LocInstitutionID INT, @LocLastMod as datetime, @LocProcessTime as datetime

SET @LocProcessTime =  getutcdate();
SET @LocInstitutionID = @InstitutionID
SET @LocLastMod = @LastMod
--END USE LOCAL VARIABLES FOR SPEED

IF (@LocLastMod) IS NULL
BEGIN
    SET @LocLastMod = '2000-01-01 01:01:01.001'
END 

SELECT 
    @LocInstitutionID as 'institution_id',
    @LocProcessTime as 'RunTime', 
    CO.Institution_ID__c AS Institution_Student_ID,
    -- 按需选择是否添加CAST，无特殊要求可以保留原代码
    CONVERT(nvarchar(25), ISNULL(T.ActivityDate, T.CreatedDate), 101) AS 'Call Date',
    CAST(ISNULL(REPLACE(REPLACE(REPLACE(SUBSTRING(T.[Description], 1, 999), ',', ';'), CHAR(13), ''), CHAR(10), ''), '') AS NVARCHAR(1000)) AS 'Notes'
FROM 
    (SELECT * 
     FROM SFAPI_Task (NOLOCK) 
     WHERE ISNULL(IsDeleted, 0) = 0) T
INNER JOIN 
    (SELECT * 
     FROM SFAPI_Case (NOLOCK) 
     WHERE ISNULL(IsDeleted, 0) = 0) CA ON CA.Id = T.WhatId
INNER JOIN 
    (SELECT * 
     FROM SFAPI_Contact (NOLOCK) 
     WHERE ISNULL(IsDeleted, 0) = 0) CO ON CO.Id = CA.ContactId
INNER JOIN 
    (SELECT * 
     FROM SFAPI_Account (NOLOCK) 
     WHERE ISNULL(IsDeleted,0) = 0) A ON A.Id = T.Accountid 
WHERE 
    T.ringdna__Call_Disposition__c IS NOT NULL 
    AND CO.Cohort__c >= 1000010
    AND ISNULL(T.ActivityDate, T.CreatedDate) >= @LocLastMod
    AND CO.Institution_ID__c = @LocInstitutionID
ORDER BY 
    CO.LRAP_ID__c, ISNULL(T.ActivityDate, T.CreatedDate) DESC

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