Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: formatting decimal places in a query in MS SQL

    Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

    i.e.,
    SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
    FROM tblNumericCovert

    where testDiv needs to spit out results like this ###.##

    I am totally lost, if anyone can help, I would appreciate it.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    cast(cast(YourValue as decimal(10,2)) as varchar(6)) + '%'

    But really, data formatting issues should be handled by the interface, not sql server. Do your formatting in Crystal, VB, ASP, Excel, or whatever else you are using to display results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It will do the rounding though:

    select right('000000'+ltrim(str(123.87745, 6, 2)), 6) + '%'

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Probably better to use ROUND for rounding, so you know what you are trying to accomplish when you look at your code...

    SELECT ROUND(127.4355,2) = 127.4400
    -bpd

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Makes sense.

  6. #6
    Join Date
    Oct 2003
    Posts
    16
    Originally posted by rdjabarov
    Makes sense.
    That works perfectly! Thanks. I've always used Access which is a breeze and I'm trying to use SQL more. I really appreciate all of y'alls help on this.

Posting Permissions

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