SQL 将数据类型 varchar 转换为 float 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9480389/
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
Error converting data type varchar to float
提问by JsonStatham
CASE
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.25 THEN (0.25+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.25 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.50 THEN (0.50+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.50 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.75 THEN (0.75+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.75 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<1 THEN (1+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))= 0 THEN (0+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
END
AS Estimated_Effort_Days,
The above code is currently rounding up a field called totaleffort to the nearest.25, for example if i have a value of 78.19 it will round up to 78.25.
上面的代码当前将名为 totaleffort 的字段向上舍入到最近的 25，例如，如果我的值为 78.19，它将向上舍入为 78.25。
I have a new requirement for the value of zero, when the value = 0 then i need to display the text 'unknown number' I have attempted to add an additional case statement however the query fails to run with an error :
我对零值有一个新要求，当值 = 0 时，我需要显示文本“未知数”我试图添加一个额外的 case 语句但是查询无法运行并出现错误：
Error converting data type varchar to float.
将数据类型 varchar 转换为 float 时出错。
Does anyone have a reccomendation for me
有没有人给我推荐
采纳答案by Andriy M
First of all, your present code returns a number. And you are trying to add a condition when it should return a string. The problem is, numeric types take precedence over string types, and so, as a result, SQL Server will try to convert your string message to a number (and fail).
首先，您当前的代码返回一个number。并且您正在尝试添加一个应该返回string 的条件。问题是，数字类型优先于字符串类型，因此，SQL Server 会尝试将您的字符串消息转换为数字（并失败）。
To avoid that, you should make sure that all numeric values you are returning are properly converted to strings, thenyou can easily add whatever message you want as a substitute for zeros.
为了避免这种情况，您应该确保您返回的所有数值都正确转换为字符串，然后您可以轻松添加任何您想要的消息来代替零。
Another thing is, your rounding technique seems to me overcomplicated. If you want to round up, just use CEILING(). If you want to round up to the nearest 0.25, you can multiply by 4, apply CEILING(), then divide by 4.
另一件事是，您的舍入技术在我看来过于复杂。如果你想四舍五入，只需使用CEILING(). 如果要四舍五入到最接近的0.25，可以乘以 4，应用CEILING()，然后除以 4。
Here's my attempt at illustrating what I mean:
这是我试图说明我的意思的尝试：
WITH data (totaleffort) AS (
SELECT CAST(123.5 AS float) UNION ALL
SELECT 88 UNION ALL
SELECT 0.067 UNION ALL
SELECT 0 UNION ALL
SELECT 9608.14
)
SELECT
ISNULL(
CAST(CAST(NULLIF(CEILING(totaleffort * 4 / 7.40) / 4, 0) AS decimal(10, 2)) AS nvarchar(30)),
'unknown number'
)
FROM data
Output:
输出：
------------------------------
16.75
12.00
0.25
unknown number
1298.50
You can also see that I'm using ISNULL()and NULLIF()here to replace 0with a custom text. It works like this:
您还可以看到我正在使用ISNULL()and NULLIF()here 替换0为自定义文本。它是这样工作的：
the calculation result is passed to
NULLIFwhose second argument is0– that means that if the result is0,NULLIFwill returnNULL, otherwise it will return the result;now
ISNULLdoes the opposite: it returns the second argument if the first one isNULL, otherwise it returns the first argument.
计算结果传递给
NULLIF它的第二个参数是0——这意味着如果结果是0，NULLIF则返回NULL，否则返回结果；现在
ISNULL正好相反：如果第一个参数是NULL，则返回第二个参数，否则返回第一个参数。
So, with this chain of transformations a zero effectively becomes 'unknown number'.
因此，通过这个转换链，零实际上变成了'unknown number'。
回答by aF.
Assuming you want to add when the value of your condition is 0, them do it like this:
假设您想在条件值为 0 时添加，他们会这样做：
CASE
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))=0 THEN
"unknown number"
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.25 THEN (0.25+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.25 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.50 THEN (0.50+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.50 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.75 THEN (0.75+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0.75 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<1 THEN (1+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))= 0 THEN (0+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))
END
AS Estimated_Effort_Days,
回答by Vikram
you can't expect to have a column where sometimes the value is varchar and and other time float, so you can convert the whole results in THEN to nvarchar like:
你不能指望有一列有时值是 varchar 和其他时间浮动，所以你可以将整个结果在 THEN 转换为 nvarchar 像：
CASE
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))=0
THEN cast('unknown number' as nvarchar)
WHEN (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))> 0 AND (Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)- FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar)))<=0.25
THEN CAST((0.25+FLOOR(Cast(CONVERT(DECIMAL(10,2),(totaleffort/7.40)) as nvarchar))) as nvarchar)
look at last line
看最后一行
回答by Diego
Why not just:
为什么不只是：
WHEN CONVERT(DECIMAL(10,2),(totaleffort/7.40)) - FLOOR(CONVERT(DECIMAL(10,2),(totaleffort/7.40)))) = 0 THEN 'unknown number'
An observation:
一个观察：
why dont you change (expression) > 0 and (same expression) <=0.25to (expression) between 0 and 0.25
你为什么不改变(expression) > 0 and (same expression) <=0.25，以 (expression) between 0 and 0.25
on the first example you are calculating the same expression twice for no reason
在第一个示例中，您无缘无故地计算了两次相同的表达式
回答by Rose
I notice that both the provided answers are doing a conversion in the 'When' part of the clause without converting the comparison value to nvarchar as well. That may be why you are still seeing errors with the provided code.
我注意到提供的两个答案都在子句的“何时”部分进行了转换，而没有将比较值转换为 nvarchar。这可能就是您仍然看到提供的代码出错的原因。
I would suggest that you leave the data type alone in the 'When' clause (it appears that the 'correct' comparison is numeric), but all the 'Then'/'Else' results need converted to character types as SQL can't mix-and-match data types in the same column.
我建议您在“When”子句中单独保留数据类型（似乎“正确”比较是数字），但所有“Then”/“Else”结果都需要转换为字符类型，因为 SQL 不能混合和匹配同一列中的数据类型。
Just a little extra input...beyond the scope of the question, I realize :-) If this is for a report, I suggest altering the report interface instead of altering the SQL. Leaving the data type alone at the view/procedure/function level will make the data structure more reusable/extensible and calculations/aggregates that shoulduse the zero value will behave as expected without having to 'reverse convert'. If you must change the SQL-side instead of the interface-side, I would suggest including both the 'report pretty' and 'actual value' columns in the SQL-side structure so you don't lose any functionality by removing zero values and changing data types.
只是一点额外的输入...超出了问题的范围，我意识到:-) 如果这是针对报告，我建议更改报告界面而不是更改 SQL。在视图/过程/函数级别单独保留数据类型将使数据结构更可重用/可扩展，并且应该使用零值的计算/聚合将按预期运行，而无需“反向转换”。如果您必须更改 SQL 端而不是接口端，我建议在 SQL 端结构中同时包含“报告漂亮”和“实际值”列，这样您就不会因删除零值而丢失任何功能和改变数据类型。
