遇到SQL错误与我的查询无关...我该怎么做才能获取我的数据？

I'm trying to run this query:

我正在尝试运行此查询:

select * from activity where id = 9348927

And I receive this error:

我收到此错误:

Conversion failed when converting the varchar value '04 30' to data type int.

将varchar值'04 30'转换为数据类型int时转换失败。

Note that I still receive this error when I change * to id, which doesn't make any sense to me. I don't receive the error when I run this:

请注意,当我将*更改为id时,我仍然会收到此错误,这对我没有任何意义。我运行时没有收到错误:

select top 32 * from activity where id = 9348927

I'm pretty sure this means that some of the data on the 33rd row is in a format that SQL Server doesn't like. Is there any way I can get it to ignore the error and display my data anyway?

我很确定这意味着第33行的某些数据采用的是SQL Server不喜欢的格式。有什么方法可以让它忽略错误并显示我的数据吗?

EDIT: id is varchar(10)

编辑:id是varchar(10)

1 个解决方案

#1

This usually means that the id column is not a number datatype, but varchar.

这通常意味着id列不是数字数据类型,而是varchar。

9348927 is a number (int) and datatype precedence means the the string value '04 30' is converted to int. Standard SQL behaviour.

9348927是数字(int),数据类型优先级表示字符串值'04 30'转换为int。标准SQL行为。

Try this:

select * from activity where id = '9348927'

Incidently, the implicit conversion means any index on id will not be used. Compare query plans.

很明显,隐式转换意味着不会使用id上的任何索引。比较查询计划。

The best way to "work around" this is to fix your data and column type. Otherwise, do the comparison as string which is what id most likely is.

“解决”这个问题的最佳方法是修复数据和列类型。否则,将比较作为字符串进行,这是最有可能的id。

#1