Thursday, December 23, 2010

T-SQL: Remove or reduce decimal places, with or without rounding

In T-SQL, this is how you can remove or reduce the number of decimal places in a number:

With rounding:


select ROUND(55.4567, 2, 0)
-- Returns 55.4600

select CAST(55.4567 as decimal(38, 2))
-- Returns 55.46

Without rounding:


select ROUND(55.4567, 2, 1)
-- Returns 55.4500

select CAST(ROUND(55.4567, 2, 1) as decimal(38, 2))
-- Returns 55.45