In an SQL SERVER 2000 table,there is a column called Kpi of float type.
when try to convert that column to varchar(cast(kpi as varchar(3))),It gives an error
Msg 232, Level 16, State 2, Line 1
Arithmetic overflow error for type varchar, value = 26.100000.
The thing is the column has only one distinct value and that is 26.1. I can not understand why does it show error when converting it into varchar!
your error has triggered by full stop in 26.1.
you have applied this command:
cast(kpi as varchar(3))
First: change cast from varchar(3) to varchar(4) (to get the first value after full stop)
So you'll have:
This line convert value 26.10000000 in 26.1
Then you apply varchar(26.1) not correct!
Varchar type wants only integer value as size.
Why you apply the last varchar?? I think you must to remove the external varchar and leave only cast function
Try this instead:
declare @x float = 26.1
select left(@x,3)
Result will be 26.
The distinct value is 26.1 ,which is 4 character length and the attempt was to cast it to cast(kpi as varchar(3))...If change it to cast(kpi as varchar(4)) it would work and ll get 26.1
