SQL中Varchar转Date时批量转换失败问题求助

场景与问题详情 #

我从大存储字段中提取yyyy-MM-dd格式的字符串（自动为varchar类型），示例字段内容如下：

EmployeeName$Test User
Email$test@test.com
ADUsername$tuser
TermDate$2023-07-13
EmployeeNumber$12345
Department$Tech

使用以下SQL查询能正确返回所有数据，TermDateString不为空且格式看似均正确：

with cte as (
    select i.IncidentNumber,
    case when charindex('TermDate$',i.symptom) = 0 or charindex('EmployeeNumber$',i.symptom) = 0 then null else
    substring(symptom,charindex('TermDate$',i.symptom) + 9,(charindex('EmployeeNumber$',i.symptom) - 2) - (charindex('TermDate$',i.symptom) + 9)) 
    end as 'TermDateString'
    from Incident i
    inner join task t on t.parentlink_recid = i.recid
    where i.subject like '%term in proc%'
    and i.status not in ('closed','cancelled','resolved')
    and i.symptom like '%EmployeeName%'
    and t.subject = 'Open WFH Equipment Return Request'
    and t.status not in ('closed','cancelled')
    --and i.incidentnumber = '5305093'
)
select *
--,convert(date,TermDateString,23) 'TermDate'
--,cast(TermDateString as date) 'TermDate'
from cte
--where convert(date,TermDateString,23) > DATEADD(month, -4, GETDATE())
order by incidentnumber desc

但取消注释日期转换语句，尝试筛选TermDateString在特定时间范围的工单时，始终报错：

Conversion failed when converting date and/or time from character string.

单条记录转换正常，批量处理则报错，用CAST替代CONVERT结果相同，已确认TermDateString无空值及首尾空格。

解决方法 #

方法1：使用TRY_CONVERT/TRY_CAST容错并定位异常记录 #

TRY_CONVERT（或TRY_CAST）在转换失败时会返回NULL，不会中断整个查询，同时可以帮你找出转换失败的异常字符串：

with cte as (
    select i.IncidentNumber,
    case when charindex('TermDate$',i.symptom) = 0 or charindex('EmployeeNumber$',i.symptom) = 0 then null else
    substring(symptom,charindex('TermDate$',i.symptom) + 9,(charindex('EmployeeNumber$',i.symptom) - 2) - (charindex('TermDate$',i.symptom) + 9)) 
    end as 'TermDateString'
    from Incident i
    inner join task t on t.parentlink_recid = i.recid
    where i.subject like '%term in proc%'
    and i.status not in ('closed','cancelled','resolved')
    and i.symptom like '%EmployeeName%'
    and t.subject = 'Open WFH Equipment Return Request'
    and t.status not in ('closed','cancelled')
)
select *
,TRY_CONVERT(date,TermDateString,23) 'TermDate'
from cte
-- 筛选有效转换的记录，或去掉此条件查看所有记录的转换结果
where TRY_CONVERT(date,TermDateString,23) > DATEADD(month, -4, GETDATE())
order by incidentnumber desc

如果要找出异常字符串，可执行：

with cte as (
    -- 原CTE逻辑不变
)
select IncidentNumber, TermDateString
from cte
where TRY_CONVERT(date,TermDateString,23) is null

方法2：优化日期字符串提取逻辑，避免无效字符混入 #

原提取逻辑依赖EmployeeNumber$的位置计算长度，若字段顺序变化或换行符格式不一致（比如部分记录是\n单换行，部分是\r\n双换行），可能提取到多余字符。可改为通过换行符定位TermDate行的结尾：

with cte as (
    select i.IncidentNumber,
    case when charindex('TermDate$',i.symptom) = 0 then null else
    substring(
        symptom,
        charindex('TermDate$',i.symptom) + 9,
        -- 用换行符（CHAR(10)）定位TermDate行的结束位置
        charindex(CHAR(10), symptom, charindex('TermDate$',i.symptom)) - (charindex('TermDate$',i.symptom) + 9)
    ) as 'TermDateString'
    from Incident i
    inner join task t on t.parentlink_recid = i.recid
    where i.subject like '%term in proc%'
    and i.status not in ('closed','cancelled','resolved')
    and i.symptom like '%EmployeeName%'
    and t.subject = 'Open WFH Equipment Return Request'
    and t.status not in ('closed','cancelled')
)
select *
,CONVERT(date,TermDateString,23) 'TermDate'
from cte
where CONVERT(date,TermDateString,23) > DATEADD(month, -4, GETDATE())
order by incidentnumber desc

此方法直接定位TermDate$所在行的结尾，无需依赖后续字段，提取的字符串更准确。

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