none
Cast demical

    Question

  • Im trying to cast decimal column

    Trying to cast a value

    example 0.969 to 96.90

                    1     to 100.00

                 0.95   to 95.00

    my example puts an extra zero at the end

    how do I prevent the extra 0

    Select id,

    ,cast(score asdecimal(5,3))*100

    from table

    thanks in Advance

    Thursday, December 6, 2018 6:38 PM

All replies

  • create table t(id int,score float)
    insert t values(1,0.969),(2,1),(3,0.95)
    
    Select id,Cast(cast(score  as decimal(5,3)) *100 as decimal(5,2))
    
    from t
    
    drop table t


    Thursday, December 6, 2018 6:57 PM
    Moderator
  • --Or this:

    Select id, Cast(score *100 as decimal(5,2)) from t


    Thursday, December 6, 2018 7:25 PM
    Moderator
  • Linton : Did you try using FORMAT function (only available from version >= SQL 2012 )

    SELECT FORMAT(0.969 * 100 ,'N1')
    SELECT FORMAT(1 * 100 ,'N1')
    SELECT FORMAT(0.95 * 100 ,'N1')
    

    Thursday, December 6, 2018 7:56 PM
  • Im trying to cast decimal column

    Trying to cast a value

    example 0.969 to 96.90

                    1     to 100.00

                 0.95   to 95.00

    my example puts an extra zero at the end

    how do I prevent the extra 0

    Select id,

    ,cast(score asdecimal(5,3))*100

    from table

    thanks in Advance

    If you want only 2 decimal places cast to decimal(10,2)

    like

    SELET CAST(Column  * 100 AS Decimal(10,2)) AS YourCOlumn FROM table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 6, 2018 8:03 PM
  • Hi Linton123,

    Per your description , you would like to delete extra 0. Right ?

     

    Please try following script.

    ---drop table test
    create table test(score float)
    insert test values(0.969),(1),(0.95)
    
    Select CONVERT(float,Cast(score*100 as decimal(5,2))) as result from test
    /*
    result
    ----------------------
    96.9
    100
    95
    */


     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Wednesday, December 12, 2018 6:47 AM
    Friday, December 7, 2018 2:11 AM
  • In RDBMS, we have a presentation layer that does this with the data it gets from the database tier. You are still writing 1960's COBOL, which has only one tier that does everything.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 7, 2018 9:18 PM
  • Hi Linton123,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 12, 2018 6:43 AM