Round function in Replace Sum query

Transact-SQL
1

Hello SQL maniacs,

I have a query which is retrieving data from a view.
In this query I use the following line:

replace (sum (column1), ',', '.') as amountpaid,

But it looks like the value I get is rounded by 1 decimal. Cause when I sum the values from the view myself than I get for example 100,86. but when I use the query I get 100,9

Can someone tell me how I get a value with 2 decimals with this query.
Thanx.

2

What is the data type of column1?

3

The column is a 'Varchar' type

4

Confirm ? You can't SUM() a varchar column.

Are you using MS SQL Server ?

5

Sorry, i was looking in the wrong column. It is a float type.
I'm using MSSQL2005 server

6

try

replace (sum (convert(decimal(10,2), column1)), ',', '.') as amountpaid
7

Thanx !!
You are an hero !!

8

Why are you replacing Comma with Period? Sounds like the Locale (controlling the numeric format you want to display the amount in) is wrong. If you change Numeric to String then you lose ability to sort etc. and may also have issues with downstream processes trying to re-parse the data. Better to transmit numeric in native format and let the "far end" deal with the display issue.

But of course I have not idea why you are needing to make this change within SQL :slight_smile: and there are also good reasons to choose to do that. I just wanted to check that your solution is the best for the actual problem you face.