Transformation of units in SQL Server

Question

I have a dataset, df, that has a column of values that are in MB. I would like to transform into TB.

MB

10000000
20000000

Desired

TB

9.09
18.18

Doing

select MB AS 'TB', (CONVERT([int],round([MB]/((1024)*(1024)),(0)))) AS TB from df

However, the result I get is

MB

0
0

I am still researching. Any suggestion is appreciated

question from:https://stackoverflow.com/questions/65877108/transformation-of-units-in-sql-server

Answer 1

/ is integer division in SQL Server. It means, that for example

SELECT 4 / 5

will return 0.

But, if you write

SELECT 4 / 5.0

you'll get 0.8

5.0 is treated as decimal type and all values in the expression are converted to decimal and division is no longer integer.

So, you can use 1024.0 constant in the expression, and all the values in it will be converted to decimal type and division will not be integer.

In the question you say that you want to show results with two decimal places, so you should not convert result to int.

select 
    [MB]
    ,round([MB]/(1024.0*1024.0), 2) AS TB 
from df