sqlsql-servert-sql

Converting bytes to kilobytes/megabytes

I have an attachments table that stores the size of the document in Bytes.

I'm needing to display a result set of all documents in either KB or MB. In KB if the document is less than 1MB or in MB if the document is larger than 1MB.

At the moment I have this within my query, but I'm struggling to get the display properly formatted in terms of rounding.

    CASE WHEN D.DocumentSize < 1000000 THEN
        CONCAT(D.DocumentSize / 1024, 'KB')
    ELSE
        CONCAT(D.DocumentSize / 1048576, 'MB')
    END AS DocumentSizeText,

Here are some example values:

87336
1458250
346
8434
8434

346 bytes is going to display 0KB also, so ideally displaying 1KB would be ideal as a minimum.

Solution

  • Use FORMAT if you want to have a specified significant number of decimal values in MB. N3 here is for showing 3 decimal numbers.

    (CASE WHEN D.DocumentSize < 1000000 THEN
           CONCAT(CEILING(D.DocumentSize / 1024.0), 'KB')
      ELSE 
           CONCAT(FORMAT(D.DocumentSize / 1048576.0, 'N3'), 'MB')
 END) AS DocumentSizeText