Arithmetic overflow error while converting varchar to decimal datatype

I am trying to convert varchar(14) to Decimal(14,2). My code is something like

Declare @varchar_variable varchar(14), @decimal_variable decimal(14,2)

Set @varchar_variable = '189'

Select @decimal_variable = Convert(Decimal(14,2),@varchar_variable)

I am only getting Arithmetic overflow error message when the value of @varchar_variable is large (like ‘10000000000189’)

Why am I getting this error message for larger values ? Are values like 10000000000189 out of range for decimal datatype ? If yes, what is the range of decimal datatype in SQL.

Answer

You need to define Decimal(16,2) to convert varchar(14) to decimal. You can alternatively use Cast instead of Convert.

Declare @varchar_variable varchar(14), @decimal_variable decimal(16,2)

Set @varchar_variable = '10000000000189'


Select @decimal_variable = CAST(@varchar_variable AS DECIMAL(16,2))

-- Select @decimal_variable = Convert(Decimal(16,2),@varchar_variable)

SELECT @decimal_variable