tSQL - Conversion from varchar to numeric works for all but integer
Actually whether there are digits or not is irrelevant. The . (dot) is forbidden if you want to cast to int. Dot can't - logically - be part of Integer definition, so even:
select cast ('7.0' as int)
select cast ('7.' as int)
will fail but both are fine for floats.
Converting a varchar
value into an int
fails when the value includes a decimal point to prevent loss of data.
If you convert to a decimal
or float
value first, then convert to int
, the conversion works.
Either example below will return 7082:
SELECT CONVERT(int, CONVERT(decimal(12,7), '7082.7758172'));
SELECT CAST(CAST('7082.7758172' as float) as int);
Be aware that converting to a float
value may result, in rare circumstances, in a loss of precision. I would tend towards using a decimal
value, however you'll need to specify precision and scale values that make sense for the varchar
data you're converting.