truncate snowflake division output scale without rounding off

Question

I have a select query having division a/b The output value is having very large scale .

The output I'm getting is

0.020011991559

I'm expecting the output below. basically 6 digits in scale

0.020011

I have tried using the TO_NUMBER function to reduce the precision.

If I do below query

SELECT TO_NUMBER(0.020011991559,28,6)

The output is coming as below, where the last digit is being rounded

0.020012

I'm only expecting the below output

0.020011

Answer 1

Welcome to floating point numbers. The numerical functions are very handy to understand how values are represented, their limits and how to make the behave to your needs. TRUNCATE or FLOOR will give the same results to what you ask for, and knowing that by default Snowflake when converting values ROUNDs is also good working knowledge.

select 
    $1 as a, 
    $2 as b,
    a/b as r1,
    truncate(a/b, 6) as r2_trunc,
    floor(a/b, 6) as r3_floor,
    round(a/b, 6) as r4_round,
    ceil(a/b, 6) as r5_ceil,
    to_number(a/b, 10, 6 ) as r6_num
from values 
    (2.561534919552, 128),
    (2.5614719872, 128),
    (10000,9999);