CODEWITHSUNDEEP
sqlsql-server
Job AJ
Job AJ

Reputation: 117

Update a column which is in money datatype by concatenating with '$' symbol

I have a table called TABLE_A which has a column called Unitcost that is in money datatype, I have to update Unitcost by ((Cost/Pack)*Count) where these Cost,Pack and Count are other columns in TABLE_A 

Note: Unitcost  is Money 
      Cost is  Money 
      Pack is int
      Count is nvarchar

TABLE_A final output should be like this with updated Unitcost 

ID   |  Cost  |  Pack  | Count  |  Unitcost 
----------------------------------------------
01   | 12     | 1      |  1     |  $12

01   | 12     | 2      |  1     |  $6

01   | 40     | 4      |  2     |  $20

01   | 12     | 1      |  2     |  $24

'$' symbol should come in the Unitcost column with value. I need a query. plz help.

Upvotes: 0

Views: 4152

Answers (3)

Louie Bao
Louie Bao

Reputation: 1732

The short answer is NO, you cannot store $ inside a money column. It can be proved with the code below:

declare @UnitCost money = '$3,257.14'
select @UnitCost -- It works but symbols like $ and , are removed. 3257.14 is returned in this case.

The code snippet below is not what you have asked for, but may just give you some ideas:

declare @UnitCost money = '3257.14'
select '$' + convert(varchar, @UnitCost, 1) -- Returns $3,257.14 as varchar.

Now, I would like to recommend that you stay away from the money/smallmoney data types, despite what the names suggest, they don't really offer any more value for financial calculations than the decimal data type. Money/smallmoney are also known to suffer from integer divisions. Refer to Throw Your MONEY Away for more details.

Upvotes: 3

criticalfix
criticalfix

Reputation: 2870

UPDATE TABLE_A
SET Unitcost = ((Cost/Pack)*Count)

SELECT ID, Cost, Pack, Count, 
'$' + CAST(Unitcost AS VARCHAR(50)) AS Unitcost
FROM TABLE_A

This assumes Pack is never zero, though.

Upvotes: 0

hungry
hungry

Reputation: 37

You can not update Unitcost Column with $ value which is having money datatype . you need to append $ to Unitcost Column when u write a Select Query. your select query would be like this.

select ID ,    Cost,    Pack,   Count,  '$' + CONVERT(VARCHAR(50), Unitcost ) AS Unitcost from TABLE_A

Other important note Your Unitcost column is based on other column multiplication.no need to create a Unitcost column in tabel as it is based on other column multiplication.you can use this select query as well 

select ID ,    Cost,    Pack,   Count,  '$' + CONVERT(VARCHAR(50), ((Cost/Pack)*Count) ) AS Unitcost from TABLE_A

Hope this will help you.

Upvotes: 0

Related Questions