Post reply

Rounding off in SQL

  • February 8, 2017 at 12:59 am

    #340454

    Here is the SQL query
    SELECT ROUND (300 /(CONVERT(decimal(4,2),54)),2) AS [Total]

    Below is my result:
    Total
    5.560000

    But I want result like this,

    Total
    5.56

    Thanks in Advance,
    Best Regards,
    Poornima

  • February 8, 2017 at 1:31 am

    #1927255

    Try

    SELECT CONVERT(decimal(4,2),ROUND (300.0 /54,2)) AS [Total]

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 8, 2017 at 1:52 am

    #1927257

    Hi,

    We should not convert the number from 300 to 300.0 because I have dynamic number coming.

    Regards,
    Poornima

  • February 8, 2017 at 3:23 am

    #1927276

    You'd need to convert it again on the outside. Like below, but why not handle this in your presentation layer? For example, on Excel a format of 0.00 would only ever show to 2 decimal places, but it stores the remainder.
    SELECT CONVERT(decimal(4,2),ROUND(300 / (CONVERT(decimal(4,2),54)),2)) AS [Total]

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 8, 2017 at 4:14 am

    #1927279

    poornima.s_pdi - Wednesday, February 8, 2017 1:52 AM

    Hi,

    We should not convert the number from 300 to 300.0 because I have dynamic number coming.

    Regards,
    Poornima

    I was not giving you an exact solution,  thought you would get the solution from my reply. As Thom suggested, it is better that you handle it in the application.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 8, 2017 at 6:43 pm

    #1927424

    Thanks Thom and Roshan Joseph.
    I tried all other functions to round off.
    Atlast I got it.

    SELECT cast(ROUND (300 /(CONVERT(decimal(10,5),54)),2) as numeric(36,2))

    This query seems to solve my issue.
    Both your answers helped me to solve my issue.
    Thanks a lot.

    Regards,
    Poornima

Viewing 6 posts - 1 through 5 (of 5 total)

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