Converting nvarchar to decimal t-sql

Question

I am trying to convert nvarchar to decimal (18,2) and I get the following message:

Msg 8115, Level 16, State 6, Line 2
Arithmetic overflow error converting nvarchar to numeric data type.

CAST: CAST(bm_onnet AS decimal(18,2)) as bm_onnet_corr , it only works if the value has a maximum of 3 decimal places, does not work for the value below:

 21.8333333333333333333333333333333333333 

How do I change my SELECT?

Answer 1

Use the round function

Example

 declare @v nvarchar(100) = '21.8333333333333333333333333333333333333' select convert(decimal(18,2),round(@v,2)) 

a select will look like this:

 SELECT CAST(round(bm_onnet,2) AS decimal(18,2)) as bm_onnet_corr 

Answer 2

 select case when charindex('.',bm_onnet)) > = 2 then convert(decimal(18,2),substring(bm_onnet,0,charindex('.',bm_onnet)+3)) else convert(decimal(18,2),bm_onnet) end 

use it instead too early in the morning.