Why ROUND() function adding too many trailing zeros

Question

create table #t(col varchar(10))
insert into #t values(1042800)

select col, ROUND(col,2) as RoundVal from #t

update #t set col = ROUND(col,2)

The select col, ROUND(col,2) from #t returns - as expected:

col	RoundVal
1042800	1042800

But update #t set col = ROUND(col,2) throws the following error:

Arithmetic overflow error for type varchar, value = 1042800.000000.

Question: I understand the error: I'm trying to convert a number into a VARCHAR variable, and the specified length of the VARCHAR variable is too short to accommodate the value. But why the update statement is adding 6 trailing zeros whereas select statement is (as expected) not adding any trailing zeros.

select SQL_Variant_Property( Round( '1042800', 2 ), 'basetype' ); returns float. select Cast( Round( '1042800', 2 ) as VarChar(32) ); returns 1.0428e+006. Some study of data type precedence is useful before using data types in curious ways, e.g. mathematical operations on strings. dbfiddle. — HABO
– HABO, Commented Jun 10 at 12:50
I'm trying to convert a number into a VARCHAR variable - Why do you want to do that? Sounds like X & Y problem, it's usually not needed — siggemannen
– siggemannen, Commented Jun 11 at 6:07

Razvan Socol · Accepted Answer · 2025-06-10 04:31:25Z

3

The ROUND function expects an "exact numeric or approximate numeric data type category" argument. When you use the ROUND function on the col column (which is a varchar(10)), it gets converted to a float data type, which does not have a fixed number of decimals (like a numeric or decimal data type).

When you try to convert the value 1042800 from a float to a varchar, it will implicitly try to use the scientific notation (because the number has more than 6 digits), which produces the result 1.0428e+006 and that will fit in a varchar(11).

See also https://dba.stackexchange.com/a/294936/29446 for an answer to a similar question.

To get the results you want, you could use:

update #t set col = CONVERT(NUMERIC(10,2),col)

This will explicitly specify a data type with 2 decimal digits, avoiding the use of scientific notation.

answered Jun 10 at 4:31

Razvan Socol

5,7492 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Turczyn · Accepted Answer · 2025-06-14 10:39:47Z

0

You are trying to assign ROUND(col,2) to nvarchar(10), so it's implicitly converter to it. That's why your select query works.

In order to better showcase the issue, you should use query:

select col, 
       cast(ROUND(col,2) as varchar(10)) as RoundVal 
from #t

which would replicate the error.

Then to see what is going you can change cast(ROUND(col,2) as varchar(10)) to nvarchar(11) and would reveal what is being saved to your column: 1.0428e+006, which is not what you expect.

answered Jun 14 at 10:39

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Why ROUND() function adding too many trailing zeros

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related