将数据类型varchar转换为浮点时出错
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,
上述代码正在将名为totaleffort的字段四舍五入为最接近的25,例如,如果我的值为78.19,它将舍入为78.25。将数据类型varchar转换为浮点时出错
我有一个零值的新要求,当值= 0,那么我需要显示文本“未知数”我试图添加一个额外的case语句,但查询无法运行的错误:
Error converting data type varchar to float.
有谁有reccomendation我
首先,你现在的代码返回一个的数字。并且您正尝试添加一个条件,当它应该返回字符串。问题是,数字类型优先于字符串类型,因此,SQL Server将尝试将字符串消息转换为数字(并失败)。
为了避免这种情况,你应该确保你所返回的所有数值正确地转换为字符串,然后你可以很容易地添加任何你想要的消息来代替零。
另一件事,你的四舍五入技术似乎对我来说过于复杂。如果你想收集,只需使用CEILING()
。如果你想四舍五入到最接近的0.25
,你可以乘以4,适用CEILING()
,然后由4
分这是我尝试在说明我的意思:
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
输出:
------------------------------
16.75
12.00
0.25
unknown number
1298.50
您还可以看到,我在此处使用ISNULL()
和NULLIF()
以用自定义文本替换0
。它的工作原理是这样的:
计算结果传递给
NULLIF
其第二个参数是0
- 这意味着,如果结果为0
,NULLIF
将返回NULL
,否则将返回的结果;现在
ISNULL
做相反的事情:如果第一个参数是NULL
,则返回第二个参数,否则返回第一个参数。
因此,通过这个转换链,零有效地变为'unknown number'
。
假设你想添加时,你的病情的值是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,
你不能期望有一个列,其中有时值为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)
看看最后一行
这不起作用 – JsonStatham 2012-02-28 13:39:02
它给出了相同的错误还是你没有得到预期的输出? – Vikram 2012-02-28 13:49:17
为什么不干脆:
WHEN CONVERT(DECIMAL(10,2),(totaleffort/7.40)) - FLOOR(CONVERT(DECIMAL(10,2),(totaleffort/7.40)))) = 0 THEN 'unknown number'
的观察:
你为什么不改变(expression) > 0 and (same expression) <=0.25
至(expression) between 0 and 0.25
您所计算同一表达式两次无故
我注意到的是,提供的答案都在“何时”的子句的一部分而不转换比较值为nvarchar做转换的第一个例子以及。这可能是为什么你仍然看到提供的代码错误。我建议你将数据类型单独留在'When'子句中(看起来“正确的”比较是数字的),但所有的'Then'/'Else'结果都需要转换为字符类型为SQL不能在同一列中混合和匹配数据类型。
只是有点额外的输入......超出了问题的范围,我意识到:-)如果这是报告,我建议改变报告界面而不是改变SQL。将数据类型单独保留在视图/过程/函数级别将使数据结构更具可重用性/可扩展性,并且应使用零值的计算/聚合将按预期行为,而不必“反向转换”。如果你必须改变SQL端而不是接口端,我建议在SQL端结构中包括'report pretty'和'actual value'列,这样你就不会因为删除零值而失去任何功能,改变数据类型。
仍然收到错误: 消息8114,级别16,状态5,行2 将数据类型nvarchar转换为float时出错。 – JsonStatham 2012-02-28 13:41:03
用我的编辑再试一次。如果它不起作用,请在您的问题中输入所有查询(或sp,如果是这种情况)。 – 2012-02-28 14:26:03