Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: Need a quick answer on decimal rounding!!

    this give me 6 digits to the right of the decimal point - can you help me round it to 2???

    CONVERT (decimal(18 , 2), 1.0 * NULLIF (vVotesR, 0) / NULLIF (vVotesR + vVotesD, 0) * 100)

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on the context in which you are using this code snippet. By itself, it returns a DECIMAL(18, 2) which only has two decimal places. Depending on the code around it, this might seem to return more decimal places.

    Using NULLIF() this way has always caused me grief. I don't think that is what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CONVERT (decimal(18 , 2), 1.0 * NULLIF (vVotesR, 0) / NULLIF (vVotesR + vVotesD, 0) * 100)
    1) Why did you multipled by 100 after division?
    2) Is NULLIF required in "NULLIF (vVotesR, 0)"?

    The following expression looks simple and understandable for me.
    CONVERT (decimal(18 , 2), 100. * vVotesR / NULLIF (vVotesR + vVotesD, 0) )

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pat rightly points out that if you are converting to decimal(18, 2) then you can only have 2 decimal places. The "2" in the datatype here denote3s the precision (i.e. the number of decimal places).

    As for NullIf() << avoid it.

    Use Coalesce() instead.

    Lastly, there's also a Round() function in SQL Server, but as mentioned your convert to decimal negates the need for this.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •