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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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[^].