Yet Another Decimal Value Question

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  May 5, 2011 at 10:17 am

  #237667

  Hi Folks

  I looked at several issues with regards to decimals but still in the dark

  I am trying to figure out how to get the actual decimal value

  as I keep getting .00 for my values

  Can someone tell me what I am doing wrong and what the format should be for logsize_gb

  Thanks

  Jim

  ********************************

  select

  CAST(name as varchar(66)) logical_name,

  database_id,

  size*8 kb_size,

  size*8/1024 mb_size,

  size*8/1024/1024 gb_size,

  CAST (size*8/1024/1024 as DECIMAL (5,2)) logsize_gb

  from

  master.sys.master_files

  where

  physical_name NOT LIKE '%.ldf'

  order by

  name;

  go

  Example Output

  logical_name database_id kb_size mb_size gb_size logsize_gb

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

  QA_WSS_Content_Home 17 30853312 30130 29 29.00

  QA_WSS_Content_Mysite 19 21351616 20851 20 20.00

  SP04_WSS_Content 63 6537408 6384 6 6.00

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 5, 2011 at 10:24 am

  #1320533

  it's integer division;

  sql shortcuts and says integer / integer = integer, s 3 / 2 = 1 for example, and not 1.5, so you lose decimal places.

  chnage it to something like this:

  select

  CAST(name as varchar(66)) logical_name,

  database_id,

  size*8.0 kb_size,

  size*8.0/1024.0 mb_size,

  size*8.0/1024.0/1024.0 gb_size,

  CAST (size*8.0/1024.0/1024.0 as DECIMAL (5,2)) logsize_gb

  from

  master.sys.master_files

  where

  physical_name NOT LIKE '%.ldf'

  order by

  name;

  go

  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!

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 5, 2011 at 10:25 am

  #1320534

  The system is defaulting to integer arithmetic.

  Try dividing by 1024.0 to force real precision.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 5, 2011 at 10:26 am

  #1320536

  Grrr, pipped at the post by Lowell 🙂

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  May 5, 2011 at 10:31 am

  #1320538

  Thanks Lowell

  so all i needed to add was ".0" then ?

  Jim

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  May 5, 2011 at 10:32 am

  #1320539

  Thanks Phil

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  May 5, 2011 at 10:40 am

  #1320543

  Ok

  gb_size is solved but am getting error for mb_size

  any idea on how to fix this one

  Thanks

  Jim

  *****************

  select

  CAST(name as varchar(66)) logical_name,

  database_id,

  CAST (size*8.0/1024.0 as DECIMAL (5,1)) mb_size

  from

  master.sys.master_files

  where

  physical_name NOT LIKE '%.ldf'

  order by

  name;

  go

  Msg 8115, Level 16, State 8, Server NSAB-SS80-SQL-N, Line 2

  Arithmetic overflow error converting numeric to data type numeric.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 5, 2011 at 10:57 am

  #1320561

  DECIMAL (5,1) means 5 significant digits: 9999.9 is the max amount...so any value larger than that size will result in an arithmatic overflow error.

  i typically always use the same decimal value that the MONEY datatype maps to ...DECIMAL(19,4) for convenience.

  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!

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  May 5, 2011 at 11:44 am

  #1320597

  Thanks Very Much Lowell

  Jim