我有一个查询,它通过子串从一个大的存储字段中获取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在特定时间范围内的票证.在将varchar转换为日期或日期时间时,通过取消注释--,convert(date,TermDateString,23) 'TermDate'
,它总是抛出以下错误:
Conversion failed when converting date and/or time from character string.
我认为它与特定记录的数据无关,因为它从不为空,并且我判断了没有前导/尾随空格.此外,我手动测试了多个记录,取消了--and i.incidentnumber = '5305093'
的注释,这是第一个结果,也是第二个结果.单独来看,这两种做法都奏效了.但是,当在--and i.incidentnumber = 'xxxx'
仍然被注释掉的情况下执行Select top 2 i.incidentnumber
时,它会抛出相同的错误,这意味着它只在try 处理多个记录时才会抛出错误.我也try 使用CAST而不是CONVERT,但效果完全相同.
有没有人知道有什么办法能让这玩意起作用?