Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select statement to reduce precision? Expand / Collapse
Author
Message
Posted Thursday, November 16, 2006 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 9, 2010 8:01 AM
Points: 30, Visits: 18

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

Post #323578
Posted Thursday, November 16, 2006 11:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836

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 - by Jeff Moden
Post #323584
Posted Thursday, November 16, 2006 11:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 25, 2010 5:28 PM
Points: 1,132, Visits: 42

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.
Post #323585
Posted Thursday, November 16, 2006 2:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 9, 2010 8:01 AM
Points: 30, Visits: 18

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

Post #323664
Posted Wednesday, June 19, 2013 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:06 AM
Points: 1, Visits: 3
how to reduce precision value
Post #1465124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse