提问人：Andrew Draper 提问时间：7/13/2023 最后编辑：Dale KAndrew Draper 更新时间：7/15/2023 访问量：97
SQL 将 varchar 转换为日期
SQL Convert Varchar to Date
问：
我有一个查询，它通过子字符串从大型存储字段中获取 yyyy-MM-dd 格式，因此该值自动为 varchar。整个记录内容的示例如下：
EmployeeName$Test User
[email protected]
ADUsername$tuser
TermDate$2023-07-13
EmployeeNumber$12345
Department$Tech
以下 SQL 查询有效并正确返回所有数据，其中 TermDateString 从不为 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')
--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'
从字符串转换日期和/或时间时转换失败。
我相信它与特定记录的数据无关，因为它永远不会为空，并且我检查了没有前导/尾随空格。此外，我通过取消注释手动测试了多条记录，这是顶部结果，也是第二个结果。单独来说，这两者都奏效了。但是，当执行 still 注释掉时，它会抛出相同的错误，这意味着它仅在尝试处理多个记录时才会抛出错误。我也尝试过使用 CAST 而不是 CONVERT，但结果完全相同。--and i.incidentnumber = '5305093'Select top 2 i.incidentnumber--and i.incidentnumber = 'xxxx'
有谁知道让它工作的方法？
答：
2赞
certifieddev0101
7/13/2023
#1
我认为错误
从字符串转换日期和/或时间时转换失败
当列中存在无效的日期值时发生。若要解决此问题并在特定时间范围内检索票证，可以按如下方式修改查询：TermDateString
SQL格式
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 *
FROM cte
WHERE TRY_CONVERT(date, TermDateString) BETWEEN '2021-01-01' AND '2021-12-31'
ORDER BY IncidentNumber DESC;
```
在这个修改后的版本中，我将转换函数替换为 ，该函数可以优雅地处理任何无效的日期值并返回 NULL，而不是抛出错误。此外，我还添加了一个条件，用于筛选所需时间范围内的值（在本例中，从 2021 年 1 月 1 日到 2021 年 12 月 31 日）。您可以根据需要调整日期范围。TRY_CONVERT(date, TermDateString)BETWEENTermDateString
通过使用 和 条件，查询应成功执行，在指定的时间范围内检索票证，而不会遇到任何转换错误。TRY_CONVERTBETWEEN
评论
0赞
Andrew Draper
7/13/2023
这很完美。感谢您的回复。奇怪的是，在 SELECT 中添加 TRY_CONVERT 会起作用并返回 0 个 null 值，这意味着没有不成功的强制转换。但是，仅使用 CONVERT 仍然会产生错误。从现在开始，我将使用TRY_CONVERT。谢谢！
0赞
Panagiotis Kanavos
7/15/2023
#2
如果无法修复数据，可以通过将 替换为 替换为 {“}”' 来将其转换为 JSON：$":"\r\n":" and surround the result with and
declare @field nvarchar(3000)='EmployeeName$Test User
[email protected]
ADUsername$tuser
TermDate$2023-07-13
EmployeeNumber$12345
Department$Tech'
declare @as_json varchar(3000)='{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
使用以下表达式将字段转换为 JSON：
'{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
您可以通过一次调用提取 并将其直接转换为 .TermDateJSON_VALUEdate
select cast(JSON_VALUE(@as_json,'$.TermDate') as date)
或者，作为单个表达式：
select cast(JSON_VALUE('{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}','$.TermDate') as date)
如果必须经常执行此操作，甚至可以使用完整表达式创建计算列，甚至为其编制索引。
您可以创建一个函数来将该格式转换为 JSON，以便更轻松地编写表达式：
create function To_Json(@field nvarchar(4000))
returns nvarchar(4000)
as
BEGIN
return '{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
END
并使用它：
select cast(JSON_VALUE(dbo.To_Json(@field),'$.TermDate') as date)
您还可以创建一个直接检索所需路径的函数：
create function Get_By_Path(@field nvarchar(4000),@path nvarchar(200))
returns nvarchar(4000)
as
BEGIN
declare @json nvarchar(4000)= '{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
return JSON_VALUE(@json,@path)
END
并在查询中使用它：
select cast(dbo.get_by_path(@field,'$.TermDate') as date)
评论
grabs a yyyy-MM-dd format from a large storage field by substringing这是一个巨大的错误。修复错误，而不是试图掩盖它。如果确实要查询某些数据，则需要将其设置为单独的字段。您可以使用 JSON 或 XML 值来存储很少查询的数据的“包”，并且仍然能够使用 XML 或 JSON 函数轻松提取其中的部分内容$":"","{""}JSON_VALUE(...)cast(JSON_VALUE(json_str,'$.TermDate') as date)