Converting numbers in SQL

I have some SQL query output for low disk space. It is displayed in MB, such as 1342 MB

I would like to convert this to GB and if possible in a decimal format such as 1.3 GB. Any suggestions?

Edit: I'm using MS SQL 2008. And I should have given way more info in my original post. Sorry. This is report in Lansweeper. Below is the current report query.

    Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname As [OS Name],
  tblAssets.Description,
  tblDiskdrives.Caption As [Partition],
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  [Free (in MB)],
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [Total Size (in MB)],
  tblDiskdrives.Lastchanged As [last changed],
  tsysOS.Image As icon
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
  5120 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <>
  0 And tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = 3 And
  tblAssetCustom.State = 1

Answers


select cast(1342/1024.0 as numeric(10,1))

Note:

  • the .0 part at the end of the 1024.0 expression is very important. Without it, you'll get the wrong result.
  • Casting as numeric(10, 1) takes care of the rounding properly.

If you're using PostgreSQL:

select pg_size_pretty( megabyteValue * 1024 * 1024 );

create function dbo.MbToGbString (@mbValue int) returns varchar (10)
as
begin
    return left (round (@mbValue / 1024.0, 1), 3) + ' GB' 
end
go


select dbo.MbToGbString (1342)

Need Your Help

How would you handle a special case in this digital logic system?

hardware boolean-logic digital-logic

I posted this digital logic diagram as an answer to another stackoverflow question. It describes a logic system which will be coded in Verilog or VHDL and eventually implemented in an FPGA.