Post reply

How to trim the decimal places?

  • May 20, 2009 at 12:36 pm

    #997822

    Have you looked up the ROUND() function in BOL yet? I'm pretty sure that this may help you solve your problem.

  • May 20, 2009 at 12:39 pm

    #997823

    In report builder, you may want the function TRUNC instead. Again, though, you will find all of this in BOL.

    Books On-Line should be your next best friend (with SSC right there with it).

  • May 20, 2009 at 2:03 pm

    #997878

    [font="Verdana"]I guess the issue is with not wanting to round the decimal places. Sadly, even using the various formatting options in SQL Server Reporting Services will round (I think.)

    So it looks like Lynn's suggestion of using the TRUNC() function in SQL Server Reporting Services will do what you want. You can tell it to truncate to 2 decimal places.

    Oh, and please please please... do not ever change numeric places by doing string conversions. There's really no need (possibly the most inefficient way I can think of). Between round() and floor() and ceiling(), you don't need to pass it via a string.

    For example, you could use:

    floor(100*MyValue)/100.0

    Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.

    [/font]

  • May 20, 2009 at 3:20 pm

    #997941

    Bruce W Cassidy (5/20/2009)

    [font="Verdana"]I guess the issue is with not wanting to round the decimal places. Sadly, even using the various formatting options in SQL Server Reporting Services will round (I think.)

    So it looks like Lynn's suggestion of using the TRUNC() function in SQL Server Reporting Services will do what you want. You can tell it to truncate to 2 decimal places.

    Oh, and please please please... do not ever change numeric places by doing string conversions. There's really no need (possibly the most inefficient way I can think of). Between round() and floor() and ceiling(), you don't need to pass it via a string.

    For example, you could use:

    floor(100*MyValue)/100.0

    Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.

    [/font]

    Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

    --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)

  • May 20, 2009 at 3:35 pm

    #997958

    I'm beginning to wondor if the OP has even checked this thread. I haven't seen anything since the original post.

  • May 20, 2009 at 4:04 pm

    #997973

    Jeff Moden (5/20/2009)

    Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

    [font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

  • May 20, 2009 at 4:22 pm

    #997984

    Bruce W Cassidy (5/20/2009)

    Jeff Moden (5/20/2009)

    Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

    [font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

    I did, that's why I suggested that OP check out ROUND() in BOL. 😉

  • May 21, 2009 at 9:08 am

    #998329

    Bruce W Cassidy (5/20/2009)

    Jeff Moden (5/20/2009)

    Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

    [font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

    Heh... I use it all the time as a "TRUNC" equivalent but thank Lynn... he was the one trying to get folks to look it up and make their own revelation on that. 😀

    That does bring up an interesting sidebar... even if I know a function cold, when I'm working on a non-high pressure job (no such thing as a low pressure job :-P), I'll take the time to revisit the functions I use as a forced refresher. I'm amazed that I still find things that I've previously missed even after writing T-SQL for years and years. Round having a 3rd operand was a revelation for me at one time, too, and I wouldn't have even thought about using it to truncate rather than round if I hadn't gone back to look at it in BOL. It takes time but it's definitely worth it.

    --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)

  • August 17, 2009 at 7:09 am

    #1040153

    Thanks a lot my problem solved from your given sugestion

    Regards,

    Tatoba

    Regards,
    Tatoba

  • August 17, 2009 at 8:59 am

    #1040253

    Thanks for the feedback, Tatoba.

    --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)

  • October 17, 2012 at 4:13 am

    #1549943

    Tnx Guys,

    This Round (Truncate) was just what i needed today 😉

    Wkr,

    Eddy

  • October 18, 2012 at 12:03 am

    #1550299

    use Round function with convert......

    SELECT convert(DECIMAL(18,2),ROUND(amount,2,1)) FROM #temp

  • October 19, 2012 at 2:00 pm

    #1551053

    If the objective is to essentially truncate the numbers after the second decimal place (and not actually round the number), use the FLOOR() function. However, it is Friday, and I may be totally misinterpreting the objective......

    DECLARE @wow4decimal(10,4)

    DECLARE @wow2decimal(10,2)

    SET @wow4 = 123.4567

    SET @wow2 = FLOOR(@wow4*100) * .01 -- FLOOR gets next lowest integer

    PRINT CONVERT(varchar(100),@wow2)

    RESULTS:

    123.45

Viewing 13 posts - 16 through 27 (of 27 total)

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