如何忽略 SQL 查询中的错误字符
简而言之，我有一个带有 varchar 字段而不是 datetime 字段的 SQL Server 数据库（不要问，这是一个很长的故事，无法修复）。不知何故，我们最近在这些字段中插入了奇怪/随机的字符，而不是应该存在的字符（NULL、'' 或 YYYY-MM-DD）。像这样：'?+x' 带有高位 ASCII 字符。
该报告使用此查询来帮助将数据整理为可用的内容（此处仅发布相关部分）：
SELECT CASE WHEN c.CallStatus = 'Closed' THEN CAST(c.ClosedDate + ' ' + c.ClosedTime as datetime) ELSE NULL END as 'Closed Date'
WHERE CAST(c.closeddate AS DATETIME) BETWEEN @StartDate AND @EndDate
但它对这个新的不良数据感到窒息。
我的问题是：
如何更新查询以忽略不良数据，以便在追查不良数据来源时运行报告？我的首要任务是让报告发挥作用，其次是找到并消除不良数据的来源。
The short story is I have a SQL Server DB with varchar fields instead of datetime (don't ask, it's a long story and can't be fixed). Somehow we've recently been getting weird / random characters inserted in these fields instead of what should be there (either NULL, '' or YYYY-MM-DD). Something like this: '?+x' with high-bit ascii characters.
The report uses this query to help massage the data into something usable (only relevant parts posted here):
SELECT CASE WHEN c.CallStatus = 'Closed' THEN CAST(c.ClosedDate + ' ' + c.ClosedTime as datetime) ELSE NULL END as 'Closed Date'
WHERE CAST(c.closeddate AS DATETIME) BETWEEN @StartDate AND @EndDate
but it is choking on this new bad data.
My question is this:
How can I update the query to ignore the bad data so I can get the reports to run while I hunt down the source of the bad data? My first priority is to get the reports to function, second is to find and kill the source of bad data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看起来一切都已修复，非常感谢！这是我的最终结果：
现在开始狩猎吧！
Looks to be all fixed, thankyouverymuch! here's my final result:
and now on to the hunt!