字符转日期/时间失败求助：含排除时间戳的SQL执行报错

问题描述 #

执行SQL代码时触发错误：Conversion failed when converting date and/or time from character string。移除最后一行排除特定时间戳的条件后查询可正常运行，但业务上需要排除该时间点的所有条目。移除WHERE子句中的日期验证条件会返回400万条结果，无法直接定位格式错误的数据。

原SQL代码 #

SELECT            c.Account_SPID__c [SPID]
                  ,CONVERT(DATE,c.Background_Check_Authorization_Date_Sent__c) [Sent]
                  ,CONVERT(DATE,c.BGC_Completion_Date__c) [Complete]
                  ,c.BGC_Status__c [BGC Status]
                  ,m2.market
                  ,a.billingstate
                  
FROM        SalesForceLocal_Reporting.dbo.Contact c WITH (NOLOCK)

inner join SalesForceLocal.dbo.Account a with (nolock) on c.account_spid__C = a.spid__C
inner join SalesForceLocal.dbo.market__C m with (nolock) on a.market__C = m.id
inner join angie.dbo.markets m2 with (nolock) on m.market_id__C = m2.marketid

where c.Background_Check_Authorization_Date_Sent__c >= DATEADD(year,-1,GETDATE())
and c.BGC_Status__c in ('pass','fail')
and c.BGC_Completion_Date__c is not null
and c.BGC_Completion_Date__c >= c.Background_Check_Authorization_Date_Sent__c

and c.background_check_authorization_date_sent__C not in 
('2023-04-18 10:45:00:00000')

问题原因 #

1. 时间戳格式错误：NOT IN中的字符串'2023-04-18 10:45:00:00000'不符合SQL datetime类型要求——毫秒部分用冒号分隔且长度为5位（标准datetime毫秒为3位，用点分隔），导致SQL转换该字符串时失败。
2. 隐式转换触发顺序：SQL查询优化器可能优先处理NOT IN条件，在未过滤列中格式错误的日期字符串前就执行转换，进而抛出错误。

解决方案 #

方案1：修正排除条件的时间戳格式 #

将错误格式的时间戳改为标准datetime格式：

and c.background_check_authorization_date_sent__C not in 
('2023-04-18 10:45:00.000')

方案2：使用安全转换函数避免坏数据触发错误 #

用TRY_CONVERT函数做安全转换，转换失败时返回NULL，不会触发错误，同时兼容列中可能存在的格式错误数据：

SELECT            c.Account_SPID__c [SPID]
                  ,TRY_CONVERT(DATE,c.Background_Check_Authorization_Date_Sent__c) [Sent]
                  ,TRY_CONVERT(DATE,c.BGC_Completion_Date__c) [Complete]
                  ,c.BGC_Status__c [BGC Status]
                  ,m2.market
                  ,a.billingstate
                  
FROM        SalesForceLocal_Reporting.dbo.Contact c WITH (NOLOCK)

inner join SalesForceLocal.dbo.Account a with (nolock) on c.account_spid__C = a.spid__C
inner join SalesForceLocal.dbo.market__C m with (nolock) on a.market__C = m.id
inner join angie.dbo.markets m2 with (nolock) on m.market_id__C = m2.marketid

where TRY_CONVERT(DATETIME, c.Background_Check_Authorization_Date_Sent__c) >= DATEADD(year,-1,GETDATE())
and c.BGC_Status__c in ('pass','fail')
and c.BGC_Completion_Date__c is not null
and TRY_CONVERT(DATETIME, c.BGC_Completion_Date__c) >= TRY_CONVERT(DATETIME, c.Background_Check_Authorization_Date_Sent__c)
and TRY_CONVERT(DATETIME, c.background_check_authorization_date_sent__C) <> '2023-04-18 10:45:00.000'

注：单个值排除用<>比NOT IN更简洁；若需排除多个值，仍可使用NOT IN，但所有值需保证格式正确，或统一用TRY_CONVERT转换后比较。

方案3：定位并修复坏数据 #

如果需要找出列中格式错误的日期数据，可执行以下查询：

SELECT c.Background_Check_Authorization_Date_Sent__c, c.BGC_Completion_Date__c
FROM SalesForceLocal_Reporting.dbo.Contact c
WHERE TRY_CONVERT(DATETIME, c.Background_Check_Authorization_Date_Sent__c) IS NULL
OR TRY_CONVERT(DATETIME, c.BGC_Completion_Date__c) IS NULL

修复这些坏数据后，再执行原查询（记得修正排除条件的时间戳格式）。

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