microsoft SQL azure studio division by zero when dividing numbers less than 1

Question

I have created this demo table -

create table main(
    val1 int,
    val2 int
);

inserted a couple of values

 insert into main values(0.54,0.56);

when I run this query

select val1/val2 from main

I get a 'Divide by zero error encountered.' This is happening because the denominator is rounded off to zero before the division happens. How do I solve this problem? When I try casting the values to numeric the error still persists.

select cast(val1 as numeric(3,2)) / cast(val2 as numeric(3,2)) 
from main

Answer 1

Check what the values in your table actually are... After your INSERT if you perform:

SELECT val1, val2
FROM dbo.main;

You'll notice that the values are 0 and 0 respectively. Therefore you get the error. 0.56 is not an int, it's a decimal, so when you INSERT it into an int, it becomes an int.

Change the data type of your columns. Perhaps:

CREATE TABLE dbo.main(val1 decimal(3,2),
                      val2 decimal(3,2));

DB<>Fiddle