1

The following statement:

SELECT ROUND(1.0 / 6.0, 6) AS RoundValue;

returns an unexpected (and wrong?) result:

0.166666

Only if I modify it like this:

SELECT ROUND(1.0 / 6.0000, 6) AS RoundValue;

it will return:

0.1666670

which has now the correct digit 7 at the end of the rounded part (but then adds a useless zero?)

This behaviour also happens when contents of columns of type NUMERIC(11,3) are used instead of float constants in the SELECT statement.

mgr
  • 344
  • 4
  • 11
  • 1
    `select system_type_name from sys.dm_exec_describe_first_result_set(N'SELECT 1.0, 6.0, 6.0000, ROUND(1.0 / 6.0, 6), ROUND(1.0 / 6.0000, 6);',null,null);`, https://stackoverflow.com/q/423925/11683. – GSerg Feb 23 '21 at 15:52
  • 1
    Those are not floating-point constants, by the way; all numeric constants with a decimal point and no suffix are `DECIMAL`s of the required precision. Floating-point would be `ROUND(1e / 6e, 6)` which gives the expected result. – Jeroen Mostert Feb 23 '21 at 18:00
  • 1
    cast as float will remove zero at the end SELECT cast(ROUND(1.0 / 6.0000, 6) as float) AS RoundValue; or cast to any numeric format you need – Power Mouse Feb 23 '21 at 19:12
  • _This behaviour also happens when contents of columns of type NUMERIC(10,2) are used instead of float constants in the SELECT statement._ No it doesn't. Have a read through [Precision, scale, and Length (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql) to understand what is happening. Dividing a numeric(10,2) value by a numeric(10,2) value yields a numeric(23,13) result and that's what the first parameter to round() will be. – AlwaysLearning Feb 23 '21 at 21:24
  • @AlwaysLearning the fields were numeric(11,3), not numeric(10,2), my mistake. I edited it in the question. Still the result is the same wrong one and my only fix is to do ROUND(field1 / (field2 + 0.000, 60) – mgr Feb 24 '21 at 09:34

1 Answers1

1

In the end I went for the pragmatic

ROUND(field1 / (field2 + 0.000))

Btw. in Oracle it works as expected.

mgr
  • 344
  • 4
  • 11