Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Convert Decimal to String is without rounding up

    Hello I'm trying to write a SQL Statement along the lines of....

    SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue

    Where stringField is a String field and decimalField is a Decimal Field in my Table.
    In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.

    How can I get it to keep the Decimal Places?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    STR(decimalField,6,2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks Man appreciated one other question.

    When I do a "SELECT decimalField FROM table" and ouput it to HTML it always outputs as 22.4500 or 132.220 etc. I could easilly remove the 0's on the right using the code behind my HTML but can I do this in SQL instead?

    I'm guessing it's done in a similar way to the above solution?

    Thanks for you help so far.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT CAST(decimalField AS INT) AS NonDecimalField FROM table
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    declare @i decimal(20,10)
    set @i = 132.2200
    select @i, ltrim(str(@i, 10, 2))
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks guys.
    Sorry derrick - that removed all decimal places - all I wanted was to remove any preceding zeros.

    rdjabarov - that worked fine thanks

Posting Permissions

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