Post reply

Decimals truncated when storing decimal in a deicmal variable or colum

  • August 27, 2013 at 10:41 am

    #149916

    Greetings All,

    I've got the following weird issue. I am performing division which is resulting in a decimal number. If I then store that number in a decimal variable or insert it into a decimal column in a table, the decimals are truncated. I've tried this in both SQL2005 & SQL2012 with the same results. Can anyone explain why this is occurring? Example code below.

    Thanks in advance,

    Casey

    declare @a decimal -- dividend

    declare @b-2 decimal -- divisor

    declare @C decimal -- result

    declare @tbl table (col1 decimal)

    set @a = 247

    set @b-2 = 192

    -- Result 1.

    select @a / @b-2

    -- Result 2.

    select @C = @a / @b-2

    select cast(@c as decimal)

    -- Result 3.

    insert @tbl (col1)

    values (@a/@b)

    select * from @tbl

  • August 27, 2013 at 10:45 am

    #1645274

    You're not declaring the precision or scale of the decimal. The default is 18,0 meaning 18 digits on the left of the decimal place and 0 on the right (so whole numbers only). If that's not what you want, you need to explicitly specify the precision and scale that you want the variable to have.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 28, 2013 at 7:21 am

    #1645554

    Hi Gail,

    Thanks so much for the reply.

    If it weren't for stupid mistakes...

    Thank you again.

    Casey

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply