CODEWITHSUNDEEP
sqlsql-server
Ali Hameed
Ali Hameed

Reputation: 11

control in display Decimal separators in sql server

I have problem in display the decimal numbers, it has many number.

My sql statement :

Select sum(HS$totalMoney)

the result :

12132.123444343

I want to display as 12132.12 without the another number

Thanks.

Upvotes: 0

Views: 155

Answers (6)

Squid
Squid

Reputation: 4810

The round function has a function parameter to truncate instead of round:

select round(12132.123444343 , 2, 1)

From here: http://msdn.microsoft.com/en-us/library/ms175003.aspx

Upvotes: 1

Zec
Zec

Reputation: 843

SELECT CONVERT(decimal(21, 2), sum(HS$totalMoney))
-- This one will round in SQL Server but truncate in ASE 15 (which was available to me at the time)

SELECT CONVERT(decimal(21, 2), round(sum(HS$totalMoney), 2, 1))
-- This one uses a variant of ROUND supported by SQL Server, but not ASE 15 (and will truncate the third and subsequent decimal places).

Upvotes: 1

Vijay
Vijay

Reputation: 8451

this query slove your problem

SELECT CAST(12132.123444343 AS DECIMAL(10,2))

or you can use

select CONVERT(decimal(18,2),round(12132.1255555 ,2))

Upvotes: 1

Bibek Gautam
Bibek Gautam

Reputation: 592

If your logic is for money you should first round the values not truncate

select CONVERT(decimal(18,2),round(12132.123444343 ,2)) gives 12132.12

select CONVERT(decimal(18,2),round(12132.125944343 ,2)) gives 12132.13

Upvotes: 2

cave
cave

Reputation: 11

if you are using mysql, use code blew

SELECT TRUNCATE(sum(HS$totalMoney), 2);

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this one -

SELECT CAST(12132.123444343 AS DECIMAL(10,2))

Upvotes: 1

Related Questions