CODEWITHSUNDEEP
sqlsql-server-2008
Grundy
Grundy

Reputation: 3

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

Upvotes: 0

Views: 164

Answers (3)

davetek
davetek

Reputation: 1

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)

Upvotes: 0

sstan
sstan

Reputation: 36503

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.

Upvotes: 1

Neil McGuigan
Neil McGuigan

Reputation: 48256

If you're using PostgreSQL:

select pg_size_pretty( megabyteValue * 1024 * 1024 );

Upvotes: 0

Related Questions