Post reply

Select statement to reduce precision?

  • November 16, 2006 at 11:43 am

    #171275

    Hello all,

    I'm sure there's a simple answer to this but it's escaping me. How can I reduce the precision of a money or smallmoney datatype in a SELECT statement so that it doesn't show 4 places to the right of the decimal?

    I tried Select CAST(FIELDNAME) as float, but it will return only one decimal point to the right if the number is a whole number.

    I want the result to ALWAYS return two decimal places.

    Thanks

  • November 16, 2006 at 11:53 am

    #672396

    You're going down the right path, but try using decimal data type and setting the precision and scale.  For example,

     

    declare @money money

    set @money = 150.2315

    select CAST(@money as decimal(18,2))

    John Rowan

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

  • November 16, 2006 at 11:55 am

    #672398

    I'm not sure what you are going after.  If this is a display issue, you may want to handle this in your front-end.  Otherwise, I do not see the problems you are having and I would absolutely not use "float". 

    DECLARE @Money TABLE( Price money)

    INSERT INTO @Money

    SELECT 1

    UNION

    SELECT 1.01

    UNION

    SELECT 1.001

    UNION

    SELECT 2.5002

    UNION

    SELECT 2.5101

    UNION

    SELECT 3.001

    UNION

    SELECT 4.5202

    UNION

    SELECT 2.515

    UNION

    SELECT 2.5555

    SELECT Price, CONVERT( decimal(5,2), Price) AS [ Price 5,2], CONVERT( float, Price) AS [ Price float], ROUND( Price, 2, 1) AS [ Price Round ]

    FROM @Money

    I wasn't born stupid - I had to study.

  • November 16, 2006 at 2:32 pm

    #672463

    John Rowan,

    Thanks for that...I forgot about the decimal type. Too bad it returns 0 instead of 0.00. I can handle that in code, though

    Farrell,

    Yeah, just a display issue. I like dealing with data issues, even if it's just display, in SQL when I can. I can change a stored procedure on the fly without having to stop debugging, change the code, then recompile.

    Thanks to you both!

    Mike

  • June 19, 2013 at 6:24 am

    #1625432

    how to reduce precision value

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

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