Error converting data type varchar to float

Question

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.

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 :

Error converting data type varchar to float.

Does anyone have a reccomendation for me

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

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.

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:

• 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;

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

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

Solution 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:

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

Solution 3

Assuming you want to add when the value of your condition is 0, them do it like this:

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,