Removing decimal places

Question

I have a set of decimal values, but I need to remove the values after the decimal if they are zero.

become

Currently, I convert to a string and replace out the trailing .00 - Is there a better method?

REPLACE(CAST(amount as varchar(15)), '.00', '')

Lots of ideas here: http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server. Note some only work on SQL 2012. — miltonb, May 27 '14 at 10:18

score 0 · Answer 1 · answered May 27 '14 at 10:24

This sounds like it is purely a data presentation problem. As such you should let the receiving application or reporting software take care of the formatting.

You could try converting the .00s to datatype int. That would truncate the decimals. However, as all the values appear in one column they will have to have the same type. Making everything an int would ruin your rows with actual decimal places.

As a SQL solution to a presentation problem, I think what you have is OK.

score 0 · Answer 2 · answered May 27 '14 at 10:33

I would advice you to compare the raw decimal value with itself floored. Example code:

declare 
  @Val1 decimal(9,2) = 17.00, 
  @Val2 decimal(9,2) = 23.50;    

select 
  case when FLOOR ( @Val1 ) = @Val1 
     then cast( cast(@Val1 as int) as varchar) 
     else cast(@Val1 as varchar) end, 
  case when FLOOR ( @Val2 ) = @Val2 
     then cast( cast(@Val2 as int) as varchar) 
     else cast(@Val2 as varchar) end
 -------------
 17   |   23.50

Removing decimal places

2 Answers2