How to trim the decimal places?

  • Hi Guys

    I have a column with the figures which have 4 decimal points.

    I want to trim them to two decimal points.

    How do I do it??

    Can it be done fron the reporting services as well??

    Thanks

  • You could use the CONVERT function for example:

    DECLARE @Num4 AS DECIMAL(10,4)

    DECLARE @Num2 AS DECIMAL(10,2)

    SET @Num4 = 1234.9876

    SET @Num2 = CONVERT(DECIMAL(10,2),@Num4)

    SELECT @Num2

    @Num2 will then equal 1234.99 -- note the rounding which has taken place.

    of course this works as well

    DECLARE @Num4 AS DECIMAL(10,4)

    DECLARE @Num2 AS DECIMAL(10,2)

    SET @Num4 = 1234.9876

    SET @Num2 = @Num4

    SELECT @Num2

    @Num2 will then equal 1234.99 -- note the rounding

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Much simpler than all that... check out the ROUND function or the STR function (which also right justifies if you need that for a report or file). 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Pls try this

    DECLARE @Num4 AS DECIMAL(10,4)

    SET @Num4 = 1234.9876

    SELECT STR(@Num4,Len(@Num4),2)

  • Nuts (9/18/2008)


    Hi Guys

    I have a column with the figures which have 4 decimal points.

    I want to trim them to two decimal points.

    How do I do it??

    Can it be done fron the reporting services as well??

    Thanks

    Post some sample data with expected result so that we dont need to guess what you really want


    Madhivanan

    Failing to plan is Planning to fail

  • Hi,

    I want to trim the value of decimal point to 2.

    For example

    1245.658797

    123597.599945

    798754.589785

    787561.124657

    I want this to be displayed as

    1245.65

    123597.59

    798754.58

    787561.12

    If i try to use decimal(15,2) it will round off the value.

    If i convert to type money then also the values get round off

    The value type is real in the database

    Please help...

  • hi,

    try this,

    create table #temp

    (

    amount decimal(15,6)

    )

    insert into #temp

    select 1245.658797

    union

    select 123597.599945

    union

    select 798754.589785

    union

    select 787561.124657

    select substring((cast(amount as char)),0,(charindex('.',(cast(amount as char)))+3)) amount from #temp

    amount

    1245.65

    123597.59

    787561.12

    798754.58

    ARUN SAS

  • DECLARE @Num4 AS DECIMAL(10,4)

    SET @Num4 = 1234.982644

    SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)

  • Hi Madhu,

    When my value is like 1567987.4699987 i got the output as

    1567987.47 which is actually rounding off the value

    DECLARE @Num4 AS DECIMAL(10,4)

    SET @Num4 = 1567987.4699987

    SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)

    And the values specified above are just examples not real values.

    Hope i am clear in my explaining the problem.

  • Using ROUND function...

    SELECT CONVERT( NUMERIC(18,2), ROUND(1245.65879, 2, 1) )

    --Ramesh


  • I think that is because of your Decimal declaration. Try this one:-

    DECLARE @Num4 AS DECIMAL(30,10)

    SET @Num4 = 1567987.4699987

    SELECT LEFT(CONVERT(VARCHAR(100),@Num4),CHARINDEX('.',CONVERT(VARCHAR(100),@Num4))+2)

    I have changed @Num4 to DECIMAL(30,10) and is working fine.

    🙂

  • Thank you very much for your responses

    As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.

    if for example if i have 40524786.406314 i want that to display as

    40524789.40.

    If i right click on properties and format that to 2 digits it rounds of to

    40524789.41

    Is there any way to find decimal point and display the right 2 numbers next to the decimal point.

    Your help would be greatful..

    Thank you

  • manjunath5581 (5/20/2009)


    Thank you very much for your responses

    As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.

    if for example if i have 40524786.406314 i want that to display as

    40524789.40.

    If i right click on properties and format that to 2 digits it rounds of to

    40524789.41

    Is there any way to find decimal point and display the right 2 numbers next to the decimal point.

    Your help would be greatful..

    Thank you

    Lookup ROUND() in BOL (Books On-Line).

  • From reporting services you should be able to put in the properties of that database field a format of n2. That should format the number for presentation purposes.

  • I tried doing that, formatting the value with n2 it rounds to .41 at the end of the sum..

    Is there any way to find the decimal point in Expression and trim the 2 numbers right to the decimal point.

    if i try to format the number it rounds of my value...

Viewing 15 posts - 1 through 15 (of 27 total)

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