CODEWITHSUNDEEP
sql-serverroundingsql-server-2014truncation
James Owen
James Owen

Reputation: 25

Can I truncate while rounding

In SQL Server 2014 I have some values that have trailing zeroes as well as some values after the decimal place 

select top 25 
    ProductName, round(UnitPrice, 1) as 'Price of Products'
from 
    Products

This query returns the result something like this:

Chai                           18.00
Chang                          19.00
Aniseed Syrup                  10.00
Chef Anton's Cajun Seasoning   22.00
Chef Anton's Gumbo Mix         21.40

My question is can I remove trailing zeroes after the decimal place while keeping any value that may be there such as the 21.40 for example while the other values just stay as 18 or 22 as round does not remove trailing zeroes

Upvotes: 0

Views: 49

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

SQL Server 2014 has FORMAT function.

If you round the price to 2 decimal digits, the formatting string should look like this:

FORMAT(ROUND(UnitPrice, 2), '0.##')

In fact, you don't need explicit ROUND, FORMAT would do it any way:

FORMAT(UnitPrice, '0.##')

Upvotes: 1

Greg
Greg

Reputation: 4055

Here's a hack, this works assuming you will always have a decimal place.

create table t1 (col1 decimal (5,2));

insert into t1 (col1) values (10.00)
insert into t1 (col1) values (10.50)

select case col1 % 1
  when 0 then PARSENAME(col1, 2)
  else col1
end
from t1

Upvotes: 0

Related Questions