SQL number decimals

Question

I run a query and all my numbers are coming out to 5 decimal points:

for example -

156713.55000
2103613.03000
2080.08000

is there a simple piece of code I can add into my code so the 'Cost' table results are to 2 decimal points?

Solution 1

Following example will help you.

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

or

Use Str() Function. It takes three arguments(the number, the number total characters to display, and the number of decimal places to display

  Select Str(12345.6789, 12, 3)

displays: ' 12345.679' ( 3 spaces, 5 digits 12345, a decimal point, and three decimal digits (679). - it rounds if it has to truncate

for a Total of 12 characters, with 3 to the right of decimal point.

Solution 2

Just use the ROUND function:

SELECT ROUND(column, 2) FROM Cost

Or to strip the decimals and round, use CAST:

SELECT CAST(column as decimal(10, 2))