How to Round to Nearest Whole Number

Transact-SQL
1

Hi experts,
I'm retrieving data file sizes:
with fs
as
(select database_id, type, (size * 8.0 / 1024) size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeInMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeInMB
from sys.databases db
ORDER bY NAME

The resultset contains a number with 6 decimal places and I want to derive a whoile number:

DBName 5120.000000 1058.437500

I've tried a few things but I can't get the result to be 5120 1058
Can anyone provide any tips? Thanks

2

Please try

Cast ( column as int )
Or
Cast ( value as int )

Hope it helps

1 Like
3

Or you could use

Round function in tsql

4

Thanks for your ideas, harishgg1. I guess I'm not sure how to plug in your suggestion to my TSQL code.
I tried this:

with fs
as
(select database_id, type, (size * 8.0 / 1024) size
from sys.master_files
)
select @@SERVERNAME,
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) CAST (DataFileSizeInMB as int),

(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeInMB

from sys.databases db
It gives this error:
Incorrect syntax near 'DataFileSizeInMB'.

5

hi jbates99

i looked at your query
i tried to understand it

i came with the SQL on my own
please verify if its OK

hope it helps
I love any feedback thanks
:slight_smile: :slight_smile:

SQL ... 
SELECT @@SERVERNAME, 
       db.NAME, 
       a.database_id, 
       Cast(( a.size * 8.0 / 1024 ) AS INT) AS DataFileSizeinMB, 
       Cast(( b.size * 8.0 / 1024 ) AS INT) AS LogFileSizeinMB 
FROM   (SELECT * 
        FROM   sys.master_files 
        WHERE  type = 0) a 
       JOIN (SELECT * 
             FROM   sys.master_files 
             WHERE  type = 1) b 
         ON a.database_id = b.database_id 
       JOIN sys.databases db 
         ON a.database_id = db.database_id

image
image.png697×230 6.37 KB

6

hi jbates99

i have given my solution to what i understood about what you were
trying to do !!

is my solution ok ??

1 Like
7

Yes harishgg1. I used Cast ( value as int ) and it worked perfectly. Thank you