Got trouble to casting value in simple query: "Arithmetic overflow error converting nvarchar to data type numeric"

Question

Here is my query

SELECT
    Id AS [ID]
    IIF(max(ISNULL(c.Value, 1.0)) = 1.0, 0.0, SUM(
                   CAST((IIF(ISNUMERIC(ISNULL(f.Value, 0.0)) = 1.0, CAST(f.Value AS float), 0.0) /
                         ISNULL(c.Value, 1.0)) *
                        60.0 AS float))) AS [Value]
FROM 
    Form f LEFT JOIN Control c ON f.ID = c.FormID
GROUP BY 
    f.Id

I get an error:

Arithmetic overflow error converting nvarchar to data type numeric

Does anyone have an idea what's wrong with my code?

Here is my data of Form table and Control table:

tbl_Control:
Id   Value          FormID
1    12200.0000016  16382 
2    0              18864
3                   18234

tbl_Form:
ID       Value
16382    5 
18864    2019-12-24T02:01:05.660Z 
318234   Test OK

@GordonLinoff Hi, I have updated the demo data above also the actual formula in my query, thanks for your request — Tung Nguyen
– Tung Nguyen, Commented Jun 18, 2020 at 4:16

Gordon Linoff · Accepted Answer · 2020-06-17 10:49:41Z

2

If you want to convert a value to a number, then uses try_convert(). For instance:

select id,
       max(try_convert(int, f.value)) as value
from form f
group by f.id;

Of course, this assumes that "number" means integer. But you can use any type -- numeric, real and so on -- if those are more appropriate.

Also: This will not return an error for overflows either. When the conversion fails, the function returns NULL.

answered Jun 17, 2020 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Got trouble to casting value in simple query: "Arithmetic overflow error converting nvarchar to data type numeric"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related