Post reply

Rounding to zero

  • December 20, 2004 at 12:54 pm

    #87352

    I am building some procs which carryout division and when i test these in query analyzer I am getting 0 returned for anything less than 0.

    ie 10/100 would return 0 instead of 0.1

    Is there some setting I need to change - I am using SQL2000 but have never had this problem before.

    Thanks.

    Regards,

    GregorB4u

  • December 20, 2004 at 3:16 pm

    #534544

    Sounds like you are performing integer division (the result of dividing one integer by another is an integer - so if the real result is < 1, the result you see is zero). You need to force real division - the following example shows one way of doing this:

    select 10/100 as IntDiv, 10/100.0 as RealDiv

    Here's another example using variables:

    declare @a integer, @b-2 integer

    set @a = 10

    set @b-2 = 100

    select @a / @b-2 as IntDev, @a / cast(@b as decimal(9)) as RealDev

    Regards

    Phil

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 21, 2004 at 7:06 am

    #534602

    Another simple example:

    declare @a integer, @b-2 integer

    set @a = 10

    set @b-2 = 100

    select (0.0 + @a) / @b-2

  • December 21, 2004 at 7:35 am

    #534612

    Thanks guys that seems to have done the trick

    Regards,

    GregorB4u

  • December 21, 2004 at 3:38 pm

    #534691

    The 'forcing' of integer division given in the examples above rely on 'implicit' conversions. Functional, yes; a best practice no. CAST/CONVERT are the functions to use in order make things 'explicit'.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 5 posts - 1 through 4 (of 4 total)

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