Problem converting nvarchar to decimal t-sql

Question

I'm trying to convert nvarchar to decimal (18,2) and I'm receiving the following message:

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

The CAST is: CAST(bm_onnet AS decimal(18,2)) as bm_onnet_corr, it works only if the value has only maximum 3 decimals, doesn't work for value below:

21.8333333333333333333333333333333333333

How should I modify my SELECT?

Solution

Use the round function

example

declare @v nvarchar(100) = '21.8333333333333333333333333333333333333'

select convert(decimal(18,2),round(@v,2))

a select would look like this

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

Comments

[Daniel Ivanov]

this would work in an sql program, I just needed an answer for a simple select, thanks anyway!

[Andriy M]

+1. CAST(CAST(bm_onnet AS float) AS decimal(18, 2)) would work as well.