SQL 将 varchar 转换为数据类型数字时出现算术溢出错误。'10' <= 9.00
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13161264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00
提问by JBond
Below is a subset of the kind of table structure and data i'm working with.
下面是我正在使用的表结构和数据类型的一个子集。
CREATE TABLE #Test
(
Val varchar(5)
,Type varchar(5)
)
INSERT #Test VALUES ('Yes','Text')
INSERT #Test VALUES ('10','Int')
INSERT #Test VALUES ('10.00','Float')
INSERT #Test VALUES ('9.00','Float')
INSERT #Test VALUES ('9','Int')
I want to write a query that will let me know if the column 'Val' is <= 9.00 (must be of numeric data type). I did this by doing the following:
我想编写一个查询，让我知道列 'Val' 是否 <= 9.00（必须是数字数据类型）。我通过执行以下操作来做到这一点：
SELECT *
FROM
(
SELECT Val
FROM #Test
WHERE Type = 'Int'
) IntsOnly
WHERE IntsOnly.Val <= 9.00
This gives me an arithmetic overflow error. However, if I exclude the row of data with the value '10':
这给了我一个算术溢出错误。但是，如果我排除值为“10”的数据行：
SELECT *
FROM
(
SELECT Val
FROM #Test
WHERE Type = 'Int'
AND Val <> '10'
) IntsOnly
WHERE IntsOnly.Val <= 9.00
It works without any issue. My question is not how to fix this as I know I can simply convert the data to the format I require.
它可以正常工作。我的问题不是如何解决这个问题，因为我知道我可以简单地将数据转换为我需要的格式。
My question is why the value of '10' in the column 'Val' is returning an error. Surely the logic should just return 'False' and simply exclude the rows because '10' (which I assume is implicitly converted) is greater than 9.00.
我的问题是为什么“Val”列中的“10”值返回错误。当然，逻辑应该只返回 'False' 并简单地排除行，因为 '10'（我假设它是隐式转换的）大于 9.00。
Thanks.
谢谢。
回答by RBarryYoung
This generates an Arithmetic Overflow because it is trying to implicitly cast the Valcolumn to a NUMERIC(3,2), which naturally will overflow on a 2-digit value like 10.
这会生成算术溢出，因为它试图将Val列隐式转换为 NUMERIC(3,2)，这自然会溢出 2 位值，如 10。
It's using NUMERIC(3,2) as the target type and size because that is the smallest numeric that 9.00appears to fit into.
它使用 NUMERIC(3,2) 作为目标类型和大小，因为这是9.00看起来适合的最小数字。
The solution, of course, is to use explict CASTing instead of doing it implicitly
当然，解决方案是使用显式 CASTing 而不是隐式执行
回答by Richard Deeming
From BOL:
来自BOL：
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
在 Transact-SQL 语句中，带有小数点的常量会自动转换为数值数据值，使用所需的最小精度和小数位数。例如，将常量 12.345 转换为精度为 5、小数位数为 3 的数值。
That means your constant 9.00will have a precision of 1 and a scale of 0a precision of 3 and a scale of 2, so it cannot store the value 10, which needs a minimum precision of 2 + scale.
这意味着您的常数9.00将具有为1的精度和标度为0的3位精度和2的比例，所以它不能存储该值10，这需要的最小精度2 + scale。
You'll need to wrap the IntsOnly.Valwith either a CASTor CONVERTto specify the correct precision and scale.
您需要IntsOnly.Val用 aCAST或包裹CONVERT以指定正确的精度和小数位数。
回答by PRAMOD MALLICK
TRY THIS...IT WORKED FOR ME
试试这个……它对我有用
CAST(CAST(@UR_VARIABLE AS FLOAT) AS NUMERIC(3,2))
