CONVERT BYTES TO MB script?
-
I have a field that is stored in bytes in sql2008 R2. I need a simple script that will convert this field to MB & round up. I'm not able to find anything but complicated functions, does anyone have a script that would b able to do this?
THANKS
-
this is about the simplest i can see... when you say round up, does that mean 3.199 meg gets rounded to four, or to 3.20?
a meg is 2^20 power, right?
/*--resultsMegabytes---------------------------------------3.20*/Declare @MyByteValue int=3355443select convert(decimal(19,2),(@MyByteValue *1.0) / power(2,20)) As MegabytesLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the quick response, my fields r stored as:
BYTES:
41645
3269
3267
3265
43084
3259
When I try to convert to MB, I get decimal numbers. Do u have the script to where it will round up & not?
I want to include this in select
THANK U
-
Also, if when I use the script u sent, I get the following:
777 0.00
475620.05
1415200.13
123400.01
Maybe I shouldn't round up? These are currently test fields, I need to verify HOW the fields are going to come in from business but I'm thinkin this isn't what they r going to want.
Thank u very much, I wasn't able to find anything simple like this.
-
ncodd (1/29/2015)
I need a simple script that will convert this field to MB & round up.Convert it to what?
just divide it by 1024 will give whole MBs. Are you setting a datatype or not, the following will just be converted to an integer and the result returned
{code="SQL"]select 41645 / 1024[/code]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Thank u very much, that solves my issue
-
Perry Whittle (1/29/2015)
ncodd (1/29/2015)
I need a simple script that will convert this field to MB & round up.Convert it to what?
just divide it by 1024 will give whole MBs. Are you setting a datatype or not, the following will just be converted to an integer and the result returned
{code="SQL"]select 41645 / 1024[/code]
i think that formula would give KiloBytes, and not Megabytes? and youd still suffer from integer division, which might affect expected results.
/*TheBytes KBytes MBytes Megabytes----------- ----------- ----------- ---------------------------------------41645 40 0 0.043269 3 0 0.003267 3 0 0.003265 3 0 0.0043084 42 0 0.043259 3 0 0.00*/;WITH MyCTE([TheBytes])AS(SELECT 41645 UNION ALLSELECT 3269 UNION ALLSELECT 3267 UNION ALLSELECT 3265 UNION ALLSELECT 43084 UNION ALLSELECT 3259)SELECT *,TheBytes/ 1024 As KBytes,TheBytes / (1024 * 1024) As MBytes,convert(decimal(19,2),(TheBytes *1.0) / power(2,20)) As MegabytesFROM MyCTE;Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
How would this apply to my byte field? These were just a few samples. IF the formula above wasn't correct, how would I use this in my select statement? I was tryin to avoid getting a function implemented & was looking for a simple way to convert my bytes to MB within my select statement.
Thank u
I do not think after analysis this will work:
SizeBytes = fieldname
select SizeBytes, convert(decimal(19,2),(SizeBytes *1.0) / power(2,20)) As Megabytes
from
TESTTABLE
-
select SizeBytes, SizeBytes / 1024 As MegabytesfromTESTTABLE-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Thank u, that works perfectly
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply