将数据类型varchar转换为float时出错

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

有没有人对我有推荐

5 个解决方案

#1

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).

首先,您的当前代码返回一个数字。而且你试图在它应该返回一个字符串时添加一个条件。问题是,数字类型优先于字符串类型,因此,SQL Server将尝试将您的字符串消息转换为数字(并失败)。

To avoid that, you should make sure that all numeric values you are returning are properly converted to strings, then you 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 0 with a custom text. It works like this:

您还可以看到我在这里使用ISNULL()和NULLIF()将0替换为自定义文本。它的工作原理如下:

• the calculation result is passed to NULLIF whose second argument is 0 – that means that if the result is 0, NULLIF will return NULL, otherwise it will return the result;

  计算结果传递给NULLIF,其第二个参数为0 - 表示如果结果为0,则NULLIF将返回NULL,否则返回结果;

• now ISNULL does the opposite: it returns the second argument if the first one is NULL, otherwise it returns the first argument.

  现在ISNULL正好相反:如果第一个参数为NULL,则返回第二个参数,否则返回第一个参数。

So, with this chain of transformations a zero effectively becomes 'unknown number'.

因此,通过这种转换链,零实际上变为“未知数”。

#2

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和其他时间浮点数,所以你可以将整个结果转换为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

看看最后一行

#3

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,

#4

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.25 to (expression) between 0 and 0.25

为什么不将(表达式)> 0和(相同表达式)<= 0.25更改为(表达式)0到0.25之间

on the first example you are calculating the same expression twice for no reason

在第一个例子中,你无缘无故地计算两次相同的表达式

#5

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.

我注意到所提供的答案都是在子句的“When”部分进行转换,而不将比较值转换为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 should use 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端结构中同时包含'report pretty'和'actual value'列,这样你就不会因删除零值而丢失任何功能。改变数据类型。