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