Conversion failed when converting the nvarchar value '3.5' to data type int with Convert()

1
votes

Column ReduceDuties.Confirmed_Time is : (Nvarchar type)

  Confirmed_Time
|----------------|
|       5        | 
|       3.5      |
|       10       |
|       15       |
|       18       |

I want to classify numbers.

Output desired:

1-9   =>  2
9-19  =>  3

I have tried:

    select t.range as [score range], count(*) as [number of occurences]
from (
      select convert (float , Confirmed_Time) as Taeid,
         case when Confirmed_Time >= 1 and Confirmed_Time <= 9 then '0-9'
         when Confirmed_Time > 9 and Confirmed_Time <= 19 then '9-19'
         end as range
     from ReduceDuties) t
group by t.range

But I get the following error :

Conversion failed when converting the nvarchar value '3.5' to data type int.

2
sqlsql-servertsql
Because 3.5 is not an integer value? - Alex Yu
@solmaz ahamdi try applying floor() in case when statement, for instance, case when floor(Confirmed_Time >= 1... - SNR
Why are you storing numerical data as an nvarchar in the first place? - Larnu
One way to fix this is to specify decimal literals in the CASE expression, like Confirmed_Time >= 1.0 and Confirmed_Time <= 9.0. - Dan Guzman
What does regional formatting have to do with storing numerical data as an nvarchar @SNR ? Apart from that formatting should be handled in the presentation layer, I don't know of any regions where 2 > 10. For the OP's data the "highest" value is '5' (and the lowest is '10'); which I doubt is intended. - Larnu

2 Answers

3
votes

Apparently, confirmed_time is a string. When you compare to an integer, it tries to compare as integers. So, make the conversion explicit:

(case when try_convert(float, Confirmed_Time) >= 1 and
           try_convert(float, Confirmed_Time) <= 9
      then '0-9'
 . . .

My recommendation is to never rely on implicit conversion. It introduces really hard to spot errors.

If you want this value as a decimal (which makes more sense to me than an int), then you should change the type in the table. If that is not possible for some reason (say some values are not numbers), you can add a new column:

alter table ReduceDuties add confirmed_time_dec as (try_convert(decimal(10, 2), Confirmed_Time));

You can then use confirmed_time_dec safely in your queries.

3
votes

The problem is that you are not doing the conversion from text to numeric inside the CASE expression. I might just start here with a CTE which does the conversion first:

WITH cte AS (
    SELECT CONVERT(float, Confirmed_Time) AS Confirmed_Time
    FROM ReduceDuties
)

SELECT
    Confirmed_Time,
    CASE WHEN Confirmed_Time BETWEEN 1 AND 9  THEN '0-9'
         WHEN Confirmed_Time BETWEEN 9 AND 19 THEN '9-19'
    END AS range
FROM cte;

If you really intend to mainly use the confirmed time column as a number, then you should probably just make it a numeric type column. Then, if you ever had to still occasionally use it as text, just do that conversion as needed.