How to convert bytes to MB and add mb at end of result in sql query

Question

2.00/5 (1 vote)

See more:

Hi We save file size in bytes,when selete data in sql query need to convert as mb and add word "MB" at end of result . This is data in "File Size" column in my table

SQL

1. I am try following ways,if file size is below 20 mb then can get result,otherwise (25327668 this 24mb in bytes) it show error like

C#

Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting nvarchar to data type numeric.

SQL

select convert(nvarchar(50),db.[File Size]/ 1048576.0 ) +' MB' as FSize from  [thumbnaa] db

2.if i try like this ,then below 1mb always show 0,pls see screeshot
http://prntscr.com/9cdhyz[^]

SQL

select convert(nvarchar(50),db.[File Size]/ (1024*1024) ) +' MB' as FSize from  [thumbnaa] db

This one work finally,but i need to show only 2 digit after dot

SQL

convert(nvarchar(50) ,cast(db.[File Size] as numeric(35,2))/1048576.0 ) +' MB' as FSize

like this 24.15 MB
pls reply asap

Regards
Aravind

Posted 9-Dec-15 15:08pm

Aravindba

Updated 13-Dec-15 15:00pm

v3

Add a Solution

Comments

Tomas Takac 10-Dec-15 5:07am

#1 works for me (SQL Server 2008R2). Are you sure that not something else is causing the error? In your example there is FTR.[Version] which clearly does not belong there. Maybe you are not showing the whole query?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-12-10T03:19:00

Solution 1

I suspect your File Size column is declared as nvarchar - you are storing numbers as strings.

Change the column type to bigint, or add an extra conversion to your query:

SQL

select convert(nvarchar(50), Convert(bigint, db.[File Size]) / 1048576.0 ) +' MB' as FSize from  [thumbnaa] db 
-- Output: 24.154346466 MB

Posted 10-Dec-15 3:19am

Richard Deeming

Comments

Aravindba 10-Dec-15 20:59pm

Hi Actually my file size column declare as Int,in this select query i am try to add "MB" at the end of result,so i convert result to nvarchar.And in description i mention below 20mb in bytes format can convert to mb successfully.

Richard Deeming 11-Dec-15 7:43am

I can't reproduce the error you've reported using the query and data you've provided unless the [File Size] column is declared as varchar / nvarchar.

With the column declared as int, your first query works perfectly.

Here's a SQLFiddle[^] to demonstrate.

Dave Kreskowiak 13-Dec-15 23:07pm

Your [File Size] column is not an int. The error says you saved the sizes as strings (nvarchar).

Aravindba 13-Dec-15 20:59pm

Ok,it will work when i change like this
convert(nvarchar(50) ,cast(db.[File Size] as numeric(35,2))/1048576.0 ) +' MB' as FSize
I need to do other thing,after dot ,only need to show first 2 digit only like 24.15 MB
How

Richard Deeming 14-Dec-15 8:06am

Use the ROUND function[^].

How to convert bytes to MB and add mb at end of result in sql query

1 solution

Solution 1

Add your solution here

Preview 0

How to convert bytes to MB and add mb at end of result in sql query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us