Post reply

Question on DataTypes

  • July 6, 2010 at 12:22 pm

    #224551

    I have a little calculation to do within a stored procedure. It's very simple - I want to multiply a number (which is always in integer) by 21 and divide it by 30

    DECLARE @Basic int, @Interval smallmoney

    SET @Basic = 4

    SET @Interval = @Basic * 21 / 30

    SELECT @Interval

    The answer to 4 * 21/30 is 2.8

    Yet, Interval contains 2.00

    What do I have to do to get it to be 2.8 (and I'd like to round it so it returns 3)?

    Sorry to ask such a basic question, it's driving me nuts. I've tried setting @Interval to be decimal(18,6) and it then shows 2.000000

    Why is it rounding it down to a whole number?

    Thanks for any help.

  • July 6, 2010 at 12:28 pm

    #1189273

    DECLARE @Basic numeric(5,2), @Interval numeric(5,2)

    SET @Basic = 4

    SET @Interval = @Basic * 21 / 30

    SELECT Round(@Interval,0)

    The @Basic*21/30 was done as integer maths (as all operands were ints), hence decimals discarded.

    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

  • July 6, 2010 at 4:40 pm

    #1189373

    Thank you.

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

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