CONVERT BYTES TO MB script?

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 8:33 am

  #314187

  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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 29, 2015 at 8:51 am

  #1774039

  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?

  /*--results

  Megabytes

  ---------------------------------------

  3.20

  */

  Declare @MyByteValue int=3355443

  select convert(decimal(19,2),(@MyByteValue *1.0) / power(2,20)) As Megabytes

  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!

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 8:54 am

  #1774044

  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

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 8:58 am

  #1774047

  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.

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  January 29, 2015 at 9:54 am

  #1774071

  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" 😉

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 9:56 am

  #1774072

  Thank u very much, that solves my issue

  🙂

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 29, 2015 at 10:55 am

  #1774106

  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.04

  3269 3 0 0.00

  3267 3 0 0.00

  3265 3 0 0.00

  43084 42 0 0.04

  3259 3 0 0.00

  */;WITH MyCTE([TheBytes])

  AS

  (

  SELECT 41645 UNION ALL

  SELECT 3269 UNION ALL

  SELECT 3267 UNION ALL

  SELECT 3265 UNION ALL

  SELECT 43084 UNION ALL

  SELECT 3259

  )

  SELECT *,

  TheBytes/ 1024 As KBytes,

  TheBytes / (1024 * 1024) As MBytes,

  convert(decimal(19,2),(TheBytes *1.0) / power(2,20)) As Megabytes

  FROM 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!

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 11:09 am

  #1774109

  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

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  January 29, 2015 at 11:17 am

  #1774118

  select SizeBytes, SizeBytes / 1024 As Megabytes

  from

  TESTTABLE

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• ncodd

  SSChasing Mays

  Points: 600

  More actions

  January 29, 2015 at 11:28 am

  #1774122

  Thank u, that works perfectly