Stanton Stanton - 25 days ago 8
SQL Question

tSQL - Conversion from varchar to numeric works for all but integer

I have a table with numbers in a

varchar(255)
field. They're all greater than one and have multiple decimal places. I'd like to convert them to integers. According to every web site I've consulted, including this one on StackOverflow, either of these should work:

SELECT CAST(VarcharCol AS INT) FROM MyTable

SELECT CONVERT(INT, VarcharCol) FROM MyTable


These both work for me for every kind of numeric value but
integer
- I can convert to
float
,
decimal
, etc. just fine, but trying to convert to
integer
gives me the following error:

Conversion failed when converting the varchar value '7082.7758172'
to data type int.


I've worked around the problem by converting to data type
Decimal(6,0)
, which works fine. But just for my education, can anyone tell me why converting to data type
int
(or
integer
) gives me an error? Thanks.

Answer

Converting a VARCHAR value into an INT fails when there are digits to the right of the decimal to prevent loss of data.

If you convert to FLOAT first, then convert to INT, the conversion works.

SELECT CAST(CAST('7082.7758172' as float) as int)

returns

7082