0

I have a column that is varchar(80) and is storing dollar amounts (no $ sign). I would like to update the value by adding another amount, e.g. the existing amount is 96.73 and I want to add 1.00 to make 97.73.

I have tried cast and convert without any luck. I have tried:

set CAVALUETEXT = convert(INT, CAVALUETEXT) + 1.00
set CAVALUETEXT = cast(cavaluetext as int) + 1.00

and get the same error:

Conversion failed when converting the varchar value '96.73' to data type int.

I think I am using SQL Server 2008.

Dale K
  • 25,246
  • 15
  • 42
  • 71
mirons
  • 1
  • 1

2 Answers2

2

You could cast to DECIMAL, do the arithmetic, and then cast back to VARCHAR:

UPDATE yourTable
SET CAVALUETEXT = CAST(
    CAST(CAVALUETEXT AS DECIMAL(19,2)) + 1.00 AS VARCHAR(21));

But the much better approach here would be to stop storing currency amounts, especially amounts on which you might need to do arithmetic, in a text columns. Use a proper type for this like DECIMAL(19,2).

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You can use CONVERT(float, [YOUR Varchar]). For example CONVERT(float, '0.123') convert it to a float 0.123. This only works on SQL Server.

Amin Gheibi
  • 639
  • 1
  • 8
  • 15