CODEWITHSUNDEEP
sqlsql-server
joeben tapel
joeben tapel

Reputation: 5

How not round up the value when adding or casting it to Money?

SELECT cast(cast(Ac.Amount as decimal(18,2)) as varchar)FROM Assessmentsurchargedetails AC

Result is: 5010.00 27.6 100.00 1000.00 10000.00

i just want to add comma separated when it hits a thousand.

Upvotes: 0

Views: 49

Answers (2)

cortijon
cortijon

Reputation: 983

You can accomplish this in three ways:

  • Floor method
  • Integer Conversion method
  • Substring Method

Here are some examples

--Integer Conversion Method
    SELECT cast(cast(cast(Amount * 100 as int) / 100.0 as decimal(18,2)) as varchar) FROM Assessmentsurchargedetails AC

or

-- FLOOR Method
SELECT cast(cast(FLOOR(Ac.Amount*100)/100.0 as decimal(18,2)) as varchar) FROM Assessmentsurchargedetails AC

or

-- Substring Method, not the increased precision on the initial decimal cast
select LEFT(cast(cast(Ac.Amount as decimal(20,4)) as varchar), cast(LOG10(Amount) as int)+4) from Assessmentsurchargedetails ac

I decided to time each of the methods by inserting about 8.25 million rows from the original Assessmentsurchargedetails table to another table. The integer conversion method took 18 seconds to process, while the other two took 16 seconds. I would recommend the FLOOR method over the Substring method, however, because in most cases you will not want to cast the end result into a varchar, and it is a much more expressive way to solve the problem.

Regarding adding a comma, the commenters are correct- use FORMAT without casting to a varchar first. In that case, your final output would be: 

SELECT FORMAT(cast(FLOOR(Ac.Amount*100)/100.0 as decimal(18,2)), '#,##0.00') FROM Assessmentsurchargedetails AC

Upvotes: 1

sam7
sam7

Reputation: 56

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')

use this REPLACE and CONVERT to get the desired results. 'varchar' doesn't support comma.

Upvotes: 0

Related Questions