decimal places are being rounded to zero

• Elizabeth Block-376102

  Grasshopper

  Points: 10

  More actions

  November 15, 2006 at 10:57 am

  #170388

  I have a simple query using an arithmetic expression that should have results in the decimal places. Instead it's rounding the decimal places to zero. Here's a simplified version of the query:

  declare @Size real

  et @Size = 2464 * 8 / 1024

  select @Size

  select cast(@Size as real)

  Here's the actual query:

  If Object_Id('tempdb..#DBSizes') is Not Null

  Drop table #DBSizes

  create table #DBSizes (

   size decimal(18,2),

   sizeMB decimal(18,2),

   maxsize real,

   name nvarchar(30),

   filename nvarchar(260)

  )

  Exec sp_MSforeachdb 'INSERT INTO #DBSizes Select cast(size as decimal(18,2)),cast((size*8)/1024 as decimal(18,2)),maxsize,name,filename From ?..sysfiles '

  select * from #DBSizes

  Does anybody have any great ideas?

  Thanks!

   

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  November 15, 2006 at 11:09 am

  #672064

  If the accuracy of a FLOAT is alright, then just force a FLOAT by adding a decimal point to one of the numbers.

  eg. set @Size = 2464.0 * 8 / 1024

  If you want to use decimals, eg money, you need to cast to the accuracy required at each stage of the calculation. This can get nasty.

   

• Elizabeth Block-376102

  Grasshopper

  Points: 10

  More actions

  November 15, 2006 at 11:11 am

  #672065

  Thanks so much! It's those stupid things that get you...

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 15, 2006 at 11:36 am

  #672075

  That's why it's called a Gotcha .